Icon

Merge Tables based on dates

Merge rows based on dates and employee number from three tables, and then fill down missing values from previous row for employee based on date order

Define columns for MA Nr and Validity based on thejoins/matches so far, so that we have a single valueto join to from the next table Table "Merge" process "Missing Value fill down" process execute to view workflowdata error(to see sample spreadsheet)T2T1T3sort in MA Nrand date orderbut the row idremovesuperflousma nr columnsMA Nr wasn't comingthrough as Intso fixed it here. Could have fixed it in transformationtab but wanted it to be obvious what had happenedset new rowidto keep ittidyMerge rows on date for T1 and T2including missingfrom both tablesMerge Join ColumnsMerge rows in from T3based on Validity date(derived from either T1 or T2)including missingMerge Join Columnsfill down from previousrename the original rowidcolumnsdon't fill downthe row id asthis allows us totrack back to theoriginal data to seewhere it came fromput row ids backon the front of the rowfor testing purposesIterate each MA Nrso that we can fill downthe missing valuesremoveoriginal row idcols for final outputrenamemergedjoin columnsOpen File or Folder Excel Reader Excel Reader Excel Reader Sorter Column Resorter Column Filter String To Number RowID Joiner Joiner Missing Value Column Rename Column Splitter Column Appender Group Loop Start Loop End Column Filter Column Rename Define columns for MA Nr and Validity based on thejoins/matches so far, so that we have a single valueto join to from the next table Table "Merge" process "Missing Value fill down" process execute to view workflowdata error(to see sample spreadsheet)T2T1T3sort in MA Nrand date orderbut the row idremovesuperflousma nr columnsMA Nr wasn't comingthrough as Intso fixed it here. Could have fixed it in transformationtab but wanted it to be obvious what had happenedset new rowidto keep ittidyMerge rows on date for T1 and T2including missingfrom both tablesMerge Join ColumnsMerge rows in from T3based on Validity date(derived from either T1 or T2)including missingMerge Join Columnsfill down from previousrename the original rowidcolumnsdon't fill downthe row id asthis allows us totrack back to theoriginal data to seewhere it came fromput row ids backon the front of the rowfor testing purposesIterate each MA Nrso that we can fill downthe missing valuesremoveoriginal row idcols for final outputrenamemergedjoin columnsOpen File or Folder Excel Reader Excel Reader Excel Reader Sorter Column Resorter Column Filter String To Number RowID Joiner Joiner Missing Value Column Rename Column Splitter Column Appender Group Loop Start Loop End Column Filter Column Rename

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: BATEB031
Created at: 2023-09-01
On NodePit since: 2023-09-02
Last update: 2024-04-27
Created with KNIME version: v4.7.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!