Log In
Register
Home
New Member
May 23, 2022
Offline
Hello,
I need some help with the following issue.
I have a data table like this:
| Company | Account | Amount | Partner |
| C1 | 100 | 1000 | P1 |
| C1 | 100 | 2000 | P2 |
| C1 | 200 | 1000 | P1 |
| C1 | 100 | 3000 | P1 |
| C2 | 400 | 1000 | P3 |
| C2 | 400 | 2000 | P4 |
I need to add some new data rows into this table based on the following "mapping table":
| Company | Account | Partner | Destination Account |
| C1 | 100 | P1 | 300 |
| C2 | 400 | P3 | 500 |
Basically, when in the data table there is a "match" on the Company+Account+Partner from the mapping table, and this could be considered a primary key in the "mapping table" and a foreign key in the "data table", a new "pair" record should be added for same amount, but switching Company with the Partner for the new account.
The resulting table should be like this:
| Company | Account | Amount | Partner |
| C1 | 100 | 1000 | P1 |
| P1 | 300 | 1000 | C1 |
| C1 | 100 | 2000 | P2 |
| C1 | 200 | 1000 | P1 |
| C1 | 100 | 3000 | P1 |
| P1 | 300 | 3000 | C1 |
| C2 | 400 | 1000 | P3 |
| P3 | 500 | 1000 | C2 |
| C2 | 400 | 2000 | P4 |
Is it possible to achieve this in PQ?
Kind Regards,
Lucian
New Member
May 23, 2022
Offline
Hello,
Meanwhile I have find out myself how to solve it: Merge the tables, expanded the required columns then do a "self append".
So no need to approve this post, but just in case it will, I have attached the "solved file" in the case I will need it again - when I forgot about it and I will search for the problem again 
Kind Regards,
Lucian
The following users say thank you to Lucian C for this useful post:
Mynda Treacy
1 Guest(s)
