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 |