Icon

02 - Excel Automation - Solution

This workflow goes along with my weekly newsletter 'The Finance Alchemist's Transformation Scroll' where I help finance professionals to make the next step in their career by automating data and spreadsheet tasks with KNIME.

If you're interested in this free newsletter (with a little bit of nerdiness inside), make sure you click the link below. It's totally free and you also can read past issues.

KNIME Rune: The JOINER Node1. Execute both Excel Reader Nodes to get the source data intoKNIME (both source files come together with this workflow, so youdon't have to download anything in addition)2. Add a JOINER Node to the workflow 2a. Connect the 1st Excel Reader output (Purchase Data)to the upper Joiner Node input 2b. Connect the 2nd Excel Reader output (Product Data) tothe lower Joiner Node input3. In the Joiner Node configuration select Product ID for bothtables as matching criterion, make also sure you checked" Matching Rows"&" Left Unmatched Rows" so you get left andmiddle yellow in the icon4. In the tab "Column Selection" keep everything from the top inputbox and only the columns product& item price in the bottom one5. Execute and say good bye to VLOOKUPs :) KNIME Charm: The ROW FILTER Node1. Add a ROW FILTER node to the top input port ofthe Joiner Node2. In the configuration make sure that "Column totest" is set to quantities, as this will have a redquestion mark (aka missing value) for the totalsrow3. Select the last option "only missing valuesmatch" 4. On the left sidebar set "Exclude Rows by theirattribute value" - this will look for rows where thecolumn quantities has a missing value (only ourtotals row has that) and exclude it from the table5. Execute see how the totals row disappears KNIME Talisman: The GROUPBY Node1. First connect a String To Number Node to the output of the RowFilter Node (we need to convert the item price to a decimalnumber, right now it's a text)2. In its configuration (double click on the node), keep only theitem price in the green box 3. Execute 4. Add a GROUPBY Node to the output port of the String to NumberNode5. Open the config and in the 1st subtab (Groups) make sure youonly have Product in the green include box (this is the categoryyou want to "group by")6. Click on the second subtab (Manual Aggregation) in the leftsidebar (Available Columns) double-click on item price. Then -once it's in the right box - click on Mean and instead select Sumas an aggregation method 7. Close the config and execute - voilà - you have summed theitem prices per Product import Purchase Dataimport Product DataaddProduct, item priceto the tabledrop thetotals row(where e. g. quantitieshas a missing value)calculate Sum(item price)perProductconvertitem pricefrom a text to a number(for calculation)Excel Reader Excel Reader Joiner Row Filter GroupBy String To Number KNIME Rune: The JOINER Node1. Execute both Excel Reader Nodes to get the source data intoKNIME (both source files come together with this workflow, so youdon't have to download anything in addition)2. Add a JOINER Node to the workflow 2a. Connect the 1st Excel Reader output (Purchase Data)to the upper Joiner Node input 2b. Connect the 2nd Excel Reader output (Product Data) tothe lower Joiner Node input3. In the Joiner Node configuration select Product ID for bothtables as matching criterion, make also sure you checked" Matching Rows"&" Left Unmatched Rows" so you get left andmiddle yellow in the icon4. In the tab "Column Selection" keep everything from the top inputbox and only the columns product& item price in the bottom one5. Execute and say good bye to VLOOKUPs :) KNIME Charm: The ROW FILTER Node1. Add a ROW FILTER node to the top input port ofthe Joiner Node2. In the configuration make sure that "Column totest" is set to quantities, as this will have a redquestion mark (aka missing value) for the totalsrow3. Select the last option "only missing valuesmatch" 4. On the left sidebar set "Exclude Rows by theirattribute value" - this will look for rows where thecolumn quantities has a missing value (only ourtotals row has that) and exclude it from the table5. Execute see how the totals row disappears KNIME Talisman: The GROUPBY Node1. First connect a String To Number Node to the output of the RowFilter Node (we need to convert the item price to a decimalnumber, right now it's a text)2. In its configuration (double click on the node), keep only theitem price in the green box 3. Execute 4. Add a GROUPBY Node to the output port of the String to NumberNode5. Open the config and in the 1st subtab (Groups) make sure youonly have Product in the green include box (this is the categoryyou want to "group by")6. Click on the second subtab (Manual Aggregation) in the leftsidebar (Available Columns) double-click on item price. Then -once it's in the right box - click on Mean and instead select Sumas an aggregation method 7. Close the config and execute - voilà - you have summed theitem prices per Product import Purchase Dataimport Product DataaddProduct, item priceto the tabledrop thetotals row(where e. g. quantitieshas a missing value)calculate Sum(item price)perProductconvertitem pricefrom a text to a number(for calculation)Excel Reader Excel Reader Joiner Row Filter GroupBy String To Number

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: m1pegv
Created at: 2023-09-14
On NodePit since: 2023-09-14
Last update: 2024-05-01
Created with KNIME version: v4.6.4

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

Try NodePit Runner!