I need to find a way to match and combine similar rows where IDs from different columns match.
This is what my table looks like:
| FirstID | SecondID | SomeMoreData |
|---|---|---|
| 123ABC | 456DEF | ... |
| 456DEF | 789GHI | ... |
| 349UYH | 286IWD | ... |
I am trying to find the rows where the FirstID and SecondID are matched.
For example, the SecondID of row 1 (456DEF) and the FirstID of row 2 are the same, so those are connected.
My plan is to make a third column to connect these two rows, with the final output being the following:
| Source ID | FirstID | SecondID | SomeMoreData |
|---|---|---|---|
| 123ABC | 123ABC | 456DEF | ... |
| 123ABC | 456DEF | 789GHI | ... |
| 349UYH | 349UYH | 286IWD | ... |
In the table above, the first two rows' values in the Source ID column are 123ABC, because the first row's FirstID is not connected to another row's SecondID, whereas the 2nd row does. For this second row, we find the original FirstID that isn't connected, and use that value as the Source ID.
Let me know if this makes sense!
My pseudocode is below:
if (SecondID is found in FirstID):
x: FirstID where SecondID = this row's FirstID
update SourceID: x
else: SourceID: this row's FirstID
It would be helpful if you provide the input in the future. You can do something like this:
q)input:([] FirstID:`123ABC`456DEF`349UYH; SecondID:`456DEF`789GHI`286IWD)
q)dictMap:(!).(select distinct SecondID,FirstID from input)`SecondID`FirstID
q)dictMap / Dict mapping if it finds the FirstID
456DEF| 123ABC
789GHI| 456DEF
286IWD| 349UYH
q)update SourceId:dictMap FirstID from input / If SourceId is empty we want to fill it using FirstID^
FirstID SecondID SourceId
-------------------------
123ABC 456DEF
456DEF 789GHI 123ABC
349UYH 286IWD
q)update SourceId:FirstID^dictMap FirstID from input
FirstID SecondID SourceId
-------------------------
123ABC 456DEF 123ABC
456DEF 789GHI 123ABC
349UYH 286IWD 349UYH