kdb q How to recognize and connect rows of data where there are same IDs

Question

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

Answer 1

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

See: https://code.kx.com/q/ref/fill/