Data Transformation Query

[Yush]

Hello,

Can someone help me (using KNIME nodes only preferably) translate this table:

Person	Activity	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21
Person A	Activity A	1	2	2	2				2					2
Person A	Activity B	2	2	2	2	2	2	2	2				2	2	3

Into this table:

Person	Activity	Start	End	Amount
Person A	Activity A	Jan-20	Jan-20	1
Person A	Activity A	Feb-20	Apr-20	2
Person A	Activity A	Aug-20	Aug-20	2
Person A	Activity A	Jan-21	Jan-21	2
Person A	Activity B	Jan-20	Aug-20	2
Person A	Activity B	Dec-20	Jan-21	2
Person A	Activity B	Feb-21	Feb-21	3

Many thanks,
Yush

[armingrudd]

Hi @Yush,

I’m not sure if this is the best approach but here is my solution:

table_transform.knwf (100.0 KB)

I used the Unpivoting node first to bring all the month columns in a single column then used 3 Lag Column nodes to compare each value in “Person”, “Activity” and “ColumnValues” (Amount) columns with the previous row. Used the first Rule Engine node to specify the “Start” row and then by using a Rank node, another Rule Engine Node and a Missing Value node I put the next corresponding rows in the same group. Finally in a Group Loop Start I have put the first the last row of each group in a single row and a few more nodes to create exactly the same table as you asked for.

[ipazin]

HI @Yush,

wouldn’t your output table be the same if you had also number 2 for for Activity A under May, June or July -20? Maybe you want to add some additional information to table in order to distinguish those cases or it is not important…

Br,
Ivan

[Yush]

If it works, then it’s an excellent solution. Thank you.
I’ve not used Rule Engine and Rank before but i’ll give it a go now.

[Yush]

Hi Ivan,

I think if you add the number 2 where you say, then the output table would become different (see below) instead:

Person	Activity	Start	End	Amount
Person A	Activity A	Jan-20	Jan-20	1
Person A	Activity A	Feb-20	Aug-20	2
Person A	Activity A	Jan-21	Jan-21	2
Person A	Activity B	Jan-20	Aug-20	2
Person A	Activity B	Dec-20	Jan-21	2
Person A	Activity B	Feb-21	Feb-21	3

Kind regards,
Yush

[armingrudd]

Sorry, I forgot to attach the workflow. I just added the workflow to my reply.

[Yush]

Many thanks, I wrestled with this for a couple of hours yesterday, nearly getting there but not quite, with way too many nodes.

[ipazin]

Hi there @Yush,

you are right. Missed something there…

Br,
Ivan