Icon

04_​Data_​Transformation_​Solution

Data Transformation - Solution

Solution to exercise 4 for the KNIME Analytics Platform for Data Wranglers course
- Transform a data table with the Unpivot node and remove superfluous rows and columns
- Format a string value using a combination of different manipulation nodes, e.g. Cell Splitter, Row Splitter, and String Manipulation



Exercise 4: Table RestructuringIn this exercise you change data structure by unpivoting columns to rows. 1a) Unpivot the columns that show the products ordered in one purchase event. Retain othercolumns in the table.1b) Remove rows that have missing values for the unpivoted column values1c) Rename "ColumnValues" column to "ProductNr" and "ShoppingNumber" to"OrderNumber"1d) Remove unnecessary columns "BasketSize" and "ColumnNames"Hint: combine tasks 1c and 1d using a single Table Manipulator node Optional2) Format the product numbers via the following steps: 2.1 Split the product numbers into substrings separated by "-" 2.2 Convert the middle part to string and add leading zeros to create a 3-char code with thepadLeft() string manipulation function 2.3 Join the manipulated substrings back to one string, and replace the product numbers bythe standardized values Split on "-"Join informationFilter Product = ?convert middle partto string,padLeft with 0Rename: "ColumnValues" and "ShoppingNumber"Remove:"BasketSize" and "ColumnNames" Cell Splitter String Manipulation Unpivoting Row Filter String Manipulation Read onsitetransactions data Table Manipulator Exercise 4: Table RestructuringIn this exercise you change data structure by unpivoting columns to rows. 1a) Unpivot the columns that show the products ordered in one purchase event. Retain othercolumns in the table.1b) Remove rows that have missing values for the unpivoted column values1c) Rename "ColumnValues" column to "ProductNr" and "ShoppingNumber" to"OrderNumber"1d) Remove unnecessary columns "BasketSize" and "ColumnNames"Hint: combine tasks 1c and 1d using a single Table Manipulator node Optional2) Format the product numbers via the following steps: 2.1 Split the product numbers into substrings separated by "-" 2.2 Convert the middle part to string and add leading zeros to create a 3-char code with thepadLeft() string manipulation function 2.3 Join the manipulated substrings back to one string, and replace the product numbers bythe standardized values Split on "-"Join informationFilter Product = ?convert middle partto string,padLeft with 0Rename: "ColumnValues" and "ShoppingNumber"Remove:"BasketSize" and "ColumnNames" Cell Splitter String Manipulation Unpivoting Row Filter String Manipulation Read onsitetransactions data Table Manipulator

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: 2023-04-05
Last update: 2024-05-01
Created with KNIME version: v4.7.2
Tags: table restructuringunpivotingmanipulationeducationETL

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

Try NodePit Runner!