I’m trying to import the attached file (it’s csv, just renamed as .txt) which contains a time-series and to pivot the times from columns to rows and combine the rows for countries which have more than one entry (Canada, Australia, China, etc.). However, I’m not sure how to do this in Knime. In Tableau Prep it’s pretty straightforward but I’m not sure what the equivalent steps are in Knime as I’m trying to learn that knowing only the basics of the tool. Each time I try to run the attached flow I get a Java heap error. I’ve updated the .ini file accordingly but it still fails. I’m really not sure that I have the pivot node configured correctly to be honest, and that may be causing the problem. Any recommendations on how to configure the pivot node for this file to pivot the dates and group the countries so that China et al only show up once in the country column? Thank you.
Hi,
I think what you are looking for here is the Unpivoting node. In that node, select the columns with the values (1/22/20, 1/23/20, etc) as Value Columns and all others as Retained Columns. Now you could follow up with a Pivoting node where the Groups is “ColumnNames”, the Pivots is “Country/Region” and the aggregation is Sum on ColumnValues.I have attached a workflow demonstrating the procedure.
Kind regards
Alexander
Thanks, that’s great info! I guess the use of the word ‘pivot’ is what was confusing me as it means different things functionality-wise in different analytics software. I would never have thought to search for, or use, ‘unpivot’.
Is there a way to group by country and aggregate, as is currently performed in the Pivot node, without doing a Pivot? Or would that require a Pivot then another Unpivot? I ask because I’d like to keep dates as columns and countries as rows as this is easier to use that way in Tableau, which I’m going to output for. Thank you!
