Icon

Match two tables and scenarios data

Report1ScenariosReport2Tabulatedto row-basedCreate counter to help us keep trackof dataTabulated torow-basedJoin report 1and report 2based on matchingcolumn and valuerename counteras Report1 IDThis tells us the matchesLeft outer jointo find the Report 1lines that match nothingKeep onlythe lines from Report 1 that don't matchThis tells us what which report 1 linesmatched with nothingTabulated torow-basedThis tells us the matches and removesmissing values where thereare partial matchesReplace "Y" withthe "column name"Ungroup againso that we canfix the missing valuefor the "All NO" scenarioReplace missingwith "*"Turn missing valueinto "*", so we canmatch it to scenarioslaterDistinct rowsWe now have a "key" (ColumnNames)for each scenarioResults of matchingand mismatchesfor Report 1Join the "key" to match scenariosMatch back to Report1to add scenario columnLeft outer join used herewhich will identify where a report 1 line matches no scenarioCreate counter to help us keep trackof datarename counteras Report2 IDRemove rowidcolumngenerated by unpivotingRemove rowidcolumngenerated by unpivotingRemove superfluouscolumnPut back inReport 1 sequenceTidy upTable Creator Table Creator Table Creator Unpivoting Counter Generation Unpivoting Joiner Column Rename GroupBy Joiner Row Filter GroupBy Unpivoting GroupBy Rule Engine Ungroup Missing Value Missing Value DuplicateRow Filter GroupBy Concatenate Joiner Joiner Counter Generation Column Rename Column Filter Column Filter DuplicateRow Filter Column Filter DuplicateRow Filter Sorter Column Filter Report1ScenariosReport2Tabulatedto row-basedCreate counter to help us keep trackof dataTabulated torow-basedJoin report 1and report 2based on matchingcolumn and valuerename counteras Report1 IDThis tells us the matchesLeft outer jointo find the Report 1lines that match nothingKeep onlythe lines from Report 1 that don't matchThis tells us what which report 1 linesmatched with nothingTabulated torow-basedThis tells us the matches and removesmissing values where thereare partial matchesReplace "Y" withthe "column name"Ungroup againso that we canfix the missing valuefor the "All NO" scenarioReplace missingwith "*"Turn missing valueinto "*", so we canmatch it to scenarioslaterDistinct rowsWe now have a "key" (ColumnNames)for each scenarioResults of matchingand mismatchesfor Report 1Join the "key" to match scenariosMatch back to Report1to add scenario columnLeft outer join used herewhich will identify where a report 1 line matches no scenarioCreate counter to help us keep trackof datarename counteras Report2 IDRemove rowidcolumngenerated by unpivotingRemove rowidcolumngenerated by unpivotingRemove superfluouscolumnPut back inReport 1 sequenceTidy upTable Creator Table Creator Table Creator Unpivoting Counter Generation Unpivoting Joiner Column Rename GroupBy Joiner Row Filter GroupBy Unpivoting GroupBy Rule Engine Ungroup Missing Value Missing Value DuplicateRow Filter GroupBy Concatenate Joiner Joiner Counter Generation Column Rename Column Filter Column Filter DuplicateRow Filter Column Filter DuplicateRow Filter Sorter Column 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: @takbb Brian Bates
Created at: 2021-06-23
On NodePit since: 2022-02-06
Last update: 2024-11-24
Created with KNIME version: v4.3.3

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

Try NodePit Runner!