Unpivoting works but a little cumbersome

KNIME Analytics Platform
1

I have my source data that looks like this: (also attaching a spreadsheet as am not able to get <table> tag work well here) Pipe symbol denotes column separator

 

 

PRODUCT_ID|MARKET_ID|My_ID|Measures|31-Oct-11|31-Oct-10|31-Oct'09
3|1|4|No_of_items_sold|171|94|96
3|2|4|No_of_items_sold|46|106|137
2|1|2|No_of_items_sold|115|104|56
2|2|2|No_of_items_sold|197|78|192
3|1|4|No_of_stores|175|129|15
3|2|4|No_of_stores|36|62|131
2|1|2|No_of_stores|141|184|142
2|2|2|No_of_stores|56|146|72
3|1|4|Value|87|95|96
3|2|4|Value|97|28|138
2|1|2|Value|181|119|198
2|2|2|Value|196|137|115

 

and am looking to get this to look like:

 

Period|PRODUCT_ID|MARKET_ID|My_ID|No_of_items|No_of_Stores|value
31-Oct-11|3|1|4|171|175|87
31-Oct-11|3|2|4|46|36|97
31-Oct-11|2|1|2|115|141|181
31-Oct-11|2|2|2|197|56|196
31-Oct-10|3|1|4|94|129|95
31-Oct-10|3|2|4|106|62|28
31-Oct-10|2|1|2|104|184|119
31-Oct-10|2|2|2|78|146|137
31-Oct'09|3|1|4|96|15|96
31-Oct'09|3|2|4|137|131|138
31-Oct'09|2|1|2|56|142|198
31-Oct'09|2|2|2|192|72|115

 

Am able to do this with Row Filter -> unpivoting -> Col_filter -> Col_rename but I have to do this 3 times for the 3 measures (no of items, no of stores, value) to get to 3 columns. 

I tried doing Unpivoting -> group by -> cell splitter and am able to get the measures into columns but am not able to get those column names programmatically. 

Looking for ideas / thoughts please...

thanks

rajeev

2

Hello,

A while ago (before there were actually Unpivoting, Pivoting) operators, I have created Unpivot that might be what you want. It is in HiTS' KNIME utilities feature. (You might also need the Merge node, as the rows will only recognized if those are in consecutive groups.)

Hope this helps, gabor

PS.: I guess there is also an R solution.

3

Awesome add-on! Worked perfectly. thanks so much

-r