From 10 to 2 columns

Dear All,

I have a table with 20 columns.

This is an example with only 10. The cells are filled with different brands.

Row ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10
1 A B C D E F G H I J
2 B A D F I J L M Z P
3 M J I K L A C B F D

I need a table with only 2 colums and the colums from above processed as follows:

start target comment (not a column ;-))
A B start = Row 1, Colum Q1; target = Row 1 Column Q2
B C start = Row 1, Colum Q2; target = Row 1 Column Q3
C D start = Row 1, Column Q3; target = Row 1, Column Q4
D E ...
E F  
F G  
G H  
H I  
... ...  
F D start = Row= 3, Colum Q9; target = Row 3; Column Q10

I got the result with with various Column Filter -, Renaming -  and Conenctrate-Nodes.

But I thouhgt there must be an easier way to get the result. 

Has anyone an idea and can help me?



Hi Chris,

you could maybe just tranpose the table and then make use of the 'Lag Column'-node?



Hi hornm,

I checked that instantly but it doesn't work because I had to setup the Lag Column node for every column. And I have over 1000 colums after transponation.



How about:

1) Unpivot (without retaining any columns);

2) Lag on ColumnValues (skip last incomplete rows);

3) Lag on ColumnNames (skip last incomplete rows + skip initial incomplete rows);

4) Column Rename: lagged column = start and the other column = target;


4 nodes, that should be it, no matter the complexity of the table. Maybe 5 if you need a rule engine node to handle the transition from Q10 (previous row) to Q1 (current row) in the original table (e.g. is there a link between J and B or not ?).

Thank you very much, Geo!

That's it!!