Icon

04_​Data_​Transformation

Exercise 4: Table RestructuringIn this exercise you change data structure by unpivoting columns to rows. 1) Change the structure of the table with the online and onsite purchases so that each purchased product is in a separate rowand not the whole purchase event 1.1 Unpivot the columns that show the products ordered in one purchase event. Retain other columns in the table. 1.2 Remove rows that have missing values for the unpivoted column values 1.3 Rename "ColumnValues" column to "ProductNr" and "ShoppingNumber" to "OrderNumber" 1.4 Remove unnecessary columns "BasketSize" and "ColumnNames" 2) (optional) Format the product numbers 1.1 Split the product numbers into substrings where separated by "-" 1.2 Separate the rows where the middle part of the product number is less than 100 1.3 Separate the rows where the middle part of the product number is less than 10 1.4 Add one zero to the middle parts that are between 10 and 99 1.5 Add two zeros to the middle parts that are smaller than 10 1.6 Concatenate the tables separated and manipulated in steps 1.2-1.5 1.7 Combine the manipulated substrings back to one string, and replace the product numbers by these manipulated values Split on "-">99>9Add "0"Add "00"Join informationRename "ColumnValues"and "ShoppingNumber"Filter Product = ?Remove"BasketSize" and"ColumnNames" Cell Splitter Row Splitter Row Splitter String Manipulation Concatenate(Optional in) String Manipulation String Manipulation Column Rename Unpivoting Row Filter Column Filter Read transactionsdata Exercise 4: Table RestructuringIn this exercise you change data structure by unpivoting columns to rows. 1) Change the structure of the table with the online and onsite purchases so that each purchased product is in a separate rowand not the whole purchase event 1.1 Unpivot the columns that show the products ordered in one purchase event. Retain other columns in the table. 1.2 Remove rows that have missing values for the unpivoted column values 1.3 Rename "ColumnValues" column to "ProductNr" and "ShoppingNumber" to "OrderNumber" 1.4 Remove unnecessary columns "BasketSize" and "ColumnNames" 2) (optional) Format the product numbers 1.1 Split the product numbers into substrings where separated by "-" 1.2 Separate the rows where the middle part of the product number is less than 100 1.3 Separate the rows where the middle part of the product number is less than 10 1.4 Add one zero to the middle parts that are between 10 and 99 1.5 Add two zeros to the middle parts that are smaller than 10 1.6 Concatenate the tables separated and manipulated in steps 1.2-1.5 1.7 Combine the manipulated substrings back to one string, and replace the product numbers by these manipulated values Split on "-">99>9Add "0"Add "00"Join informationRename "ColumnValues"and "ShoppingNumber"Filter Product = ?Remove"BasketSize" and"ColumnNames" Cell Splitter Row Splitter Row Splitter String Manipulation Concatenate(Optional in) String Manipulation String Manipulation Column Rename Unpivoting Row Filter Column Filter Read transactionsdata

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: Maarit Laukkanen
Created at: 2019-10-18
On NodePit since: 2019-12-07
Last update: 2024-04-27
Created with KNIME version: v4.0.2

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!