Icon

Automate Away - webinar demo workflow

There has been no title set for this workflow's metadata.

Workflow accompanying the KNIME "Automate Away" webinar

5 October 2023


Thanks for those who joined the webinar. I hope you found it useful!


@takbb Brian Bates

Product File RESOURCE 1 - SALES FILE READ AND CLEAN we can use the missing valuesnode to fill down Then... we do something similar for Sales Lists(pre-prepared) convert Sales Date to LocalDate from String RESOURCE 2- ADDITIONAL VLOOKUPS A lookup table forregions. Works justlike we did for productcode "Advanced Options" on alookup table for productcategoriesAllows lookup usingwildcards, regex or substring RESOURCE 3 - CALCULATIONS Add tax amount calculation:Qty x Price x Sales Tax (already divided by 100 !)(rounded to 2 decimal places)NOTE:There are different rounding algorithms:roundHalfUp(n,dp) is equivalent to Excel's round(n,dp)when rounding each "rounding digit" (those which are to be dropped, plus the finalretained digit are each incremented by 1 (rounded up) if the digit which followed is (oncerounded) between 5 and 9.roundHalfDown(n,dp) is similar, but are rounded up only when the rounding digits arebetween 6 and 9, so "5" (i.e. the "half-way" point ) rounds downward.KNIME's round(n,dp) a.k.a "bankers rounding" and "round_even" alternates betweenHalfUp and HalfDown depending on whether the digit preceding the rounding digit isodd or even, to reduce potential "bias" of cumulative rounding. Any rounding digit that is5 is rounded toward the even digit. RESOURCE 6 - USER FILE SELECTION RESOURCE 4 (OPTIONAL) - TIDY UP FOR OUTPUT TO EXCEL RESOURCE 5 (OPTIONAL) - ADDITIONAL AGGREGATION AND BLANK-DOWNfor "Sales Report" sheet execute this component to view workflow files in data areajulyNode 3Node 4Node 5Node 6Node 7Node 8sales tax and productNode 10Read in entire file, and then we'll useadditional nodes to split out what wewantCONFIGURED AS JULY!we have split the table at "Invoice Number" and want to use thelower output, which we then need to inspect to determine which rows we needWe can now fix the column namesbased on the first rowand have it attemptto set data typesso let's do a regex filter againusing INV[0-9]+and again by using the splitter we get to see whatwe've kept, and what we've lostSelect sales listfile to be processedLocatesale-list*filesDetermine the paths forboth Sales and Productsfile for the selectionregionsFind Product CategoriesLocal Table of ProductCategoriesFind Region Namestax amountsale valuesale valueincl taxQty to Integer(Sales List)reorder columns, rename columns, hide existing columnsshow/hide any "new" columnsSort into Region,Invoice Numberand Product Name orderConvert salesdate stringto Local DatedatatypeFill down missinginvoice number, sales date, regionNode 305aggregate by regionNode 307Node 308pull in period labelNode 310Totals of Tax Amount, Sale Value and Sale inc TaxNode 313format the spreadsheetusing Continental NodesThe Continental Nodesextension must be installed!Additional "Bonus"component that I wanted todemo in the webinarbut ran out of time!This does the opposite of "fill down"and blanks strings where they are repeatedfor ease of reading.So I'm doing this just for the Sales Report tab Open File or Folder Excel Reader Table Splitter Row to Column Names Row Splitter String To Number Row Splitter String Manipulation Cross Joiner Table Cropper Excel Reader Table Splitter Row to Column Names Row Splitter Table View(JavaScript) List Files/Folders Get XLSX File Names Excel Reader Value Lookup Table Creator Value Lookup Math Formula Math Formula Math Formula String To Number Table Manipulator Sorter String to Date&Time Missing Value Value Lookup Row Aggregator Concatenate Cell Extractor Cell Updater Excel Writer Row Aggregator Concatenate XLSX Simple ExcelFormat Apply Blank Down Strings Product File RESOURCE 1 - SALES FILE READ AND CLEAN we can use the missing valuesnode to fill down Then... we do something similar for Sales Lists(pre-prepared) convert Sales Date to LocalDate from String RESOURCE 2- ADDITIONAL VLOOKUPS A lookup table forregions. Works justlike we did for productcode "Advanced Options" on alookup table for productcategoriesAllows lookup usingwildcards, regex or substring RESOURCE 3 - CALCULATIONS Add tax amount calculation:Qty x Price x Sales Tax (already divided by 100 !)(rounded to 2 decimal places)NOTE:There are different rounding algorithms:roundHalfUp(n,dp) is equivalent to Excel's round(n,dp)when rounding each "rounding digit" (those which are to be dropped, plus the finalretained digit are each incremented by 1 (rounded up) if the digit which followed is (oncerounded) between 5 and 9.roundHalfDown(n,dp) is similar, but are rounded up only when the rounding digits arebetween 6 and 9, so "5" (i.e. the "half-way" point ) rounds downward.KNIME's round(n,dp) a.k.a "bankers rounding" and "round_even" alternates betweenHalfUp and HalfDown depending on whether the digit preceding the rounding digit isodd or even, to reduce potential "bias" of cumulative rounding. Any rounding digit that is5 is rounded toward the even digit. RESOURCE 6 - USER FILE SELECTION RESOURCE 4 (OPTIONAL) - TIDY UP FOR OUTPUT TO EXCEL RESOURCE 5 (OPTIONAL) - ADDITIONAL AGGREGATION AND BLANK-DOWNfor "Sales Report" sheet execute this component to view workflow files in data areajulyNode 3Node 4Node 5Node 6Node 7Node 8sales tax and productNode 10Read in entire file, and then we'll useadditional nodes to split out what wewantCONFIGURED AS JULY!we have split the table at "Invoice Number" and want to use thelower output, which we then need to inspect to determine which rows we needWe can now fix the column namesbased on the first rowand have it attemptto set data typesso let's do a regex filter againusing INV[0-9]+and again by using the splitter we get to see whatwe've kept, and what we've lostSelect sales listfile to be processedLocatesale-list*filesDetermine the paths forboth Sales and Productsfile for the selectionregionsFind Product CategoriesLocal Table of ProductCategoriesFind Region Namestax amountsale valuesale valueincl taxQty to Integer(Sales List)reorder columns, rename columns, hide existing columnsshow/hide any "new" columnsSort into Region,Invoice Numberand Product Name orderConvert salesdate stringto Local DatedatatypeFill down missinginvoice number, sales date, regionNode 305aggregate by regionNode 307Node 308pull in period labelNode 310Totals of Tax Amount, Sale Value and Sale inc TaxNode 313format the spreadsheetusing Continental NodesThe Continental Nodesextension must be installed!Additional "Bonus"component that I wanted todemo in the webinarbut ran out of time!This does the opposite of "fill down"and blanks strings where they are repeatedfor ease of reading.So I'm doing this just for the Sales Report tabOpen File or Folder Excel Reader Table Splitter Row to Column Names Row Splitter String To Number Row Splitter String Manipulation Cross Joiner Table Cropper Excel Reader Table Splitter Row to Column Names Row Splitter Table View(JavaScript) List Files/Folders Get XLSX File Names Excel Reader Value Lookup Table Creator Value Lookup Math Formula Math Formula Math Formula String To Number Table Manipulator Sorter String to Date&Time Missing Value Value Lookup Row Aggregator Concatenate Cell Extractor Cell Updater Excel Writer Row Aggregator Concatenate XLSX Simple ExcelFormat Apply Blank Down Strings

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: bbate
Created at: 2023-10-05
On NodePit since: 2023-10-06
Last update: 2024-11-21
Created with KNIME version: v5.1.1

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

Try NodePit Runner!