Forum Discussion
Aggregate alternate rows
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???
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
6 Replies
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
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
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
Well done Mark, that's nailed it.
Many thanks.
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.
