Forum Discussion
Aggregate alternate rows
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
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
