Aggregate alternate rows

[wooand]

OK, so this one has me totally stumped.

I have a list of swap transactions, where financial instruments are exchanged on day one, and are swapped back on day x at the end of the term. 

For each transaction I have a reference, a trade date, a buy or sell and a rate.  For each transaction I then have the opposite data on the next line.  This means I have a table with all the correct data.

Now I need to put the data in line 2 alongside the data in line 1 so that I have a single line per transaction.  I can then add the details I need to add into a new column and off we go.  Except I have no clue how to place the details in line 2 into columns next to the original line 1 trade.

Any clues???  

[MarkS · Accepted solution]

Hi @wooand,

To get the second line of data you could try this pattern.

1. Add a new index column starting at 1

2. Add another new index column starting at 0

3. Merge Query -  using the same table (left outer join)  merge on the index columns (first index column from the table shown on the top with the second index column from the table on the bottom.

4. Transform one of the index columns with modulo 2 (from the standard math transformations)

5. Filter out that column on 1

6. Expand the table

MarkS

[wooand]

Mark,

I have a question.  I have been using your answer to this question for some time with great success, but I am struggling when I have two rows the same.  See below - lines 5 and 6 are the same amount - but when I use this process to bring the lines alongside one another the duiplicated information seems to create a problem in that it only mirrors the row once, thereby making every line one line out.  I've tried using 'Full Outer' but that didn't work.  

Any ideas?

A.

[MarkS]

Hi @wooand,

I cannot re-create what you are experiencing.  When I try it this is what I am getting:

 

[wooand]

Well done Mark, that's nailed it.

Many thanks.

[Zubair_Muhammad]

Hi @wooand

If each transaction has a unique reference and 2 rows only, I believe it should be doable

Could you paste some sample data and expected reults?

May be 10-15 rows of sample data and your desired RESULT

[wooand]

Thanks Zubair.  I've taken the amounts out, but I'm sure you get the gist:

Pair	Trade Date	Value Date	Trade Type	CUST Side Allocation	Amount	BUY CCY	SELL CCY	USD Cost	USD Amount
EURCHF	03/01/2017	06/01/2017	SWAP	SELL	10.00	CHF	EUR	1.00	10.00
EURCHF	03/01/2017	11/01/2017	SWAP	BUY	10.00	EUR	CHF	1.00	10.00
EURCHF	03/01/2017	06/01/2017	SWAP	BUY	10.00	EUR	CHF	1.00	10.00
EURCHF	03/01/2017	11/01/2017	SWAP	SELL	10.00	CHF	EUR	1.00	10.00
EURGBP	03/01/2017	01/02/2017	SWAP	BUY	10.00	EUR	GBP	1.00	10.00
EURGBP	03/01/2017	04/01/2017	SWAP	SELL	10.00	GBP	EUR	1.00	10.00
EURGBP	03/01/2017	04/01/2017	SWAP	SELL	10.00	GBP	EUR	1.00	10.00
EURGBP	03/01/2017	01/02/2017	SWAP	BUY	10.00	EUR	GBP	1.00	10.00
EURGBP	03/01/2017	31/01/2017	SWAP	SELL	10.00	GBP	EUR	1.00	10.00
EURGBP	03/01/2017	05/01/2017	SWAP	BUY	10.00	EUR	GBP	1.00	10.00
EURGBP	03/01/2017	31/01/2017	SWAP	BUY	10.00	EUR	GBP	1.00	10.00
EURGBP	03/01/2017	05/01/2017	SWAP	SELL	10.00	GBP	EUR	1.00	10.00
EURGBP	03/01/2017	11/01/2017	SWAP	BUY	10.00	EUR	GBP	1.00	10.00
EURGBP	03/01/2017	06/01/2017	SWAP	SELL	10.00	GBP	EUR	1.00	10.00

And the target is to have the far legs currently on the second row captured alongside their near legs:

Pair

Trade Date

Value Date

Trade Type

CUST Side Allocation

Amount

BUY CCY

SELL CCY

USD Cost

USD Amount

Value Date

Trade Type

CUST Side Allocation

Amount

BUY CCY

SELL CCY

USD Cost

USD Amount

EURCHF

03/01/2017

06/01/2017

SWAP

SELL

10.00

CHF

EUR

1.00

10.00

11/01/2017

SWAP

BUY

10.00

EUR

CHF

1.00

10.00

EURCHF

03/01/2017

06/01/2017

SWAP

BUY

10.00

EUR

CHF

1.00

10.00

11/01/2017

SWAP

SELL

10.00

CHF

EUR

1.00

10.00

EURGBP

03/01/2017

01/02/2017

SWAP

BUY

10.00

EUR

GBP

1.00

10.00

04/01/2017

SWAP

SELL

10.00

GBP

EUR

1.00

10.00

EURGBP

03/01/2017

04/01/2017

SWAP

SELL

10.00

GBP

EUR

1.00

10.00

01/02/2017

SWAP

BUY

10.00

EUR

GBP

1.00

10.00

EURGBP

03/01/2017

31/01/2017

SWAP

SELL

10.00

GBP

EUR

1.00

10.00

05/01/2017

SWAP

BUY

10.00

EUR

GBP

1.00

10.00

EURGBP

03/01/2017

31/01/2017

SWAP

BUY

10.00

EUR

GBP

1.00

10.00

05/01/2017

SWAP

SELL

10.00

GBP

EUR

1.00

10.00

EURGBP

03/01/2017

11/01/2017

SWAP

BUY

10.00

EUR

GBP

1.00

10.00

06/01/2017

SWAP

SELL

10.00

GBP

EUR

1.00

10.00