From 10 to 2 columns

KNIME Analytics Platform
1

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?

Cheers,

Chris

2

Hi Chris,

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

Cheers,

Martin

3

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.

Cheers,

Chris

4

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 ?).

5

Thank you very much, Geo!

That's it!!