How do I loop through same ID in many rows?

[jonatasflausino]

Hello,

I have one simple database where the same number appears in two columns and I would like to get the full chain.

Database example (also .xlsx attached):

INPUT

Cod1	Cod2
1	2
2	3
3	4
5	6
6	7
8	9
10	11
11	12

RESULT THAT I LOOK FOR

Col1	Col2	Col3	Col4
1	2	3	4
2	3	4
3	4
5	6	7
6	7
8	9
10	11	12
11	12

I appreciate the assistance.
Example.xlsx (9.9 KB)

[Daniel_Weikert]

I could think of using a lag node for column 1 and check if col2 = lag col 1.
Maybe someone comes up with a better idea
br

[iCFO]

Or a “negative lag” to look down Cod2 in order to test if it falls sequentially so that it can be moved to a new column.

Lag Column with Negative value?

Hi there! You are right! Using Moving Aggregation you can simulate negative lag interval in Lag Column node. Window type is Forward, window length-1 determines the lag interval and aggregation is Last on lagging column. Additionally there is another way using just a bit of filtering where number of excluded rows determines the lag interval [NegLagColumn] Workflow is attached so check it out and if any questions feel free to ask. 2019_04_03_ReverseLagColumnOption.knwf (16.2 KB) Rega…

This negative lag style approach may also now be possible just using the Column Expression node with the recent addition of multi-row support, but I haven’t had time to delve into it yet…

[iCFO]

I guess you could also loop through rows in one Cod1, append it to a “transformed” Cod2, test for when the columns fail to fall in sequential order, then delete the remaining columns, then concatenate your row results into a table at loop end. Slower processing, but it should work as an alternate option.

[bruno29a]

Hi @jonatasflausino and welcome to the Knime Community.

Is it similar to this?

Parent-Child Hierarchy

Dear Knime community! I would like to know whether a node exist that can create the column “path” with the columns “Child ID” and “Parent ID” as inputs: [image] This is a standard function in DAX (PowerBI /SAAS language), which is very useful for Business Intelligence applications. Thank you very much for any tips! Dimitri

[jonatasflausino]

Hello, @bruno29a.

This worked like a charm, thanks a lot.
I just needed to split later the cell into columns.

Many thanks for your support!

[iCFO]

I wasn’t thinking about it along the lines of Parent / Child relationship without the context in the sample data. Great approach @bruno29a! I am going to incorporate this into projects as well!