Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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???
Solved! Go to 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
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
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.
Well done Mark, that's nailed it.
Many thanks.
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 |