Icon

01_​Transform_​Using_​Group_​By_​and_​Joiner_​nodes

Transform Data using GroupBy and Joiner nodes
Move from a series ofcontracts to a one-rowsummary for eachcustomer. Extract only some customers Find the list of unique customers accordingto Country using Group By nodeStep 1: Double Click on the GroupBy node to open thedialog boxStep 2: In the "Groups" tab. Select the string column"country". In the "Manual aggregation" tab, select the string column"Cust_ID" and aggregation as "Unique concatenate"Step 3 : Select "OK" and close the dialog box, now rightclick on the node and select on execute to get the list ofcustomer Ids according to the country Join the Tables using Joiner Node Step 1: Connect the tables to be joined to the input port ofJoiner nodeStep 2: Open the dialog box and in "Joiner Settings" tabselect the key column in both the tables that will be used tojoin themStep 3: In the "Include in Output" section select the type ofjoinStep 4: Select "OK" and close the dialog box, now right clickon the node and select on execute to get the output of thejoin operation Concatenate the results using Concatenate nodeStep 1: Drag the "Concatenate" node to KNIME AnalyticsPlatformStep 2: Click on the three dots in the bottom corner of thenode, to add one extra input port for the node. Connect theOutputs of the Row Filter nodes to the Input ports of"Concatenate" nodeStep 3: Open the Dialog box and decide on the option forthe "Duplicate row ID handling" and "Column handling"Step 4: Select "OK" and close the dialog box, now right clickon the node and select on execute to get the output of thejoin operation excludecolumn "card"For each customer:- total sum of amount- countries active in- customer since ...- time in days between first and last purchasefind which customersare active in which countrysort byascending datecountry = *Germany*country = *USA*1000 < sum(amount) < 2000sum(amount) > 5000convert dates from String to DateTimeformat dd.MM.yyyysales records 2008-2011inner join of original datawith aggregationson Cust_IDwrite to csv filesum(amount) < 5000country = *Brazil* Column Filter GroupBy GroupBy Sorter Row Filter Row Filter Row Filter Row Filter Concatenate String to Date&Time File Reader Joiner CSV Writer Row Filter Row Filter Move from a series ofcontracts to a one-rowsummary for eachcustomer. Extract only some customers Find the list of unique customers accordingto Country using Group By nodeStep 1: Double Click on the GroupBy node to open thedialog boxStep 2: In the "Groups" tab. Select the string column"country". In the "Manual aggregation" tab, select the string column"Cust_ID" and aggregation as "Unique concatenate"Step 3 : Select "OK" and close the dialog box, now rightclick on the node and select on execute to get the list ofcustomer Ids according to the country Join the Tables using Joiner Node Step 1: Connect the tables to be joined to the input port ofJoiner nodeStep 2: Open the dialog box and in "Joiner Settings" tabselect the key column in both the tables that will be used tojoin themStep 3: In the "Include in Output" section select the type ofjoinStep 4: Select "OK" and close the dialog box, now right clickon the node and select on execute to get the output of thejoin operation Concatenate the results using Concatenate nodeStep 1: Drag the "Concatenate" node to KNIME AnalyticsPlatformStep 2: Click on the three dots in the bottom corner of thenode, to add one extra input port for the node. Connect theOutputs of the Row Filter nodes to the Input ports of"Concatenate" nodeStep 3: Open the Dialog box and decide on the option forthe "Duplicate row ID handling" and "Column handling"Step 4: Select "OK" and close the dialog box, now right clickon the node and select on execute to get the output of thejoin operation excludecolumn "card"For each customer:- total sum of amount- countries active in- customer since ...- time in days between first and last purchasefind which customersare active in which countrysort byascending datecountry = *Germany*country = *USA*1000 < sum(amount) < 2000sum(amount) > 5000convert dates from String to DateTimeformat dd.MM.yyyysales records 2008-2011inner join of original datawith aggregationson Cust_IDwrite to csv filesum(amount) < 5000country = *Brazil* Column Filter GroupBy GroupBy Sorter Row Filter Row Filter Row Filter Row Filter Concatenate String to Date&Time File Reader Joiner CSV Writer Row Filter Row Filter

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: rosy
Created at: 2017-01-31
On NodePit since: 2022-08-21
Last update: 2024-05-02
Created with KNIME version: v4.5.1
Tags: ETLKNIMEdata manipulationOnboardingspreadsheetsJoinerGroupByBeginnerBeginner Cheat SheetBeginners Space

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

Try NodePit Runner!