Icon

TableDIFF

Compare two Tables (extended).

This workflow uses Ipazin work to compares 2 tables that have one KEY_ID field and same structure meaning they have same number of columns and matching columns have same names and types.
Identifies old key records, new key ones and unchanged key ones.
For changed fields, the comparison is done in a loop using Column Comparator node.
In each loop iteration, two columns are compared and one additional column with values TRUE or FALSE is created.
Aditionaly writes one excel file with separated sheets for deleted, inserted, updated and unchanged records

Forum link:
https://forum.knime.com/t/data-set-comparison/18507
https://hub.knime.com/ipazin/spaces/Public/latest/2019_10_03_Comparing_Two_Tables~xU8aDokP89N2Y59x



Extended Compare two TablesThis workflow uses Ipazin work to compares 2 tables columns that have one KEY_ID field and same structure meaningthey have same number of columns and matching columns have same names and types. Identifies old key records, new key ones and unchanged key ones.For changed fields, the comparison is done in a loop using Column Comparator node. In each loop iteration, two columns are compared and one additional column with values TRUE or FALSE is created.Aditionaly writes one excel file with separated sheets for deleted, inserted, updated and unchanged records 1) First, we create a comparation field, with all column names in a collection “ALL_FIELDS” column 2) Second, we find match and unmatched KEY_ID3) Those KEY_ID that are in the new table, but not in reference table, were “INSERTED ONES”4) Those KEY_ID that are NOT in the new table, but are in reference table, were “DELETED ONES”5) Those KEY_ID that match both tables can be compared for changes on “ALL_FIELDS” columna. If all fields match in both tables, then “SAME ONES”b. If any field from reference table unmatches new table, them find the “DIFFERENTES ONES”Forum link: https://forum.knime.com/t/data-set-comparison/18507https://hub.knime.com/ipazin/spaces/Public/latest/2019_10_03_Comparing_Two_Tables~xU8aDokP89N2Y59xUpdated 2023-01-02 correction for (right) columns REFERENCETABLESEPARATENEW FROMOLDCOLLECTIONKEYCOLLECTIONKEYINSERTEDONESDELETEDONESSAMEKEY_IDSAMEONESDIFFERENTONESColumnsto includeSAMEKEY_IDNewTABLEMISSINGSTRINGMISSINGSTRINGINSERTEDDELETEDDIFFERENTSAMEonlycolumns Table Creator Joiner Create CollectionColumn Create CollectionColumn Column Filter Column Filter Joiner Column Filter CHANGES Columns Variables DuplicateRow Filter Table Creator Missing Value Missing Value Excel Writer Column Filter Extended Compare two TablesThis workflow uses Ipazin work to compares 2 tables columns that have one KEY_ID field and same structure meaningthey have same number of columns and matching columns have same names and types. Identifies old key records, new key ones and unchanged key ones.For changed fields, the comparison is done in a loop using Column Comparator node. In each loop iteration, two columns are compared and one additional column with values TRUE or FALSE is created.Aditionaly writes one excel file with separated sheets for deleted, inserted, updated and unchanged records 1) First, we create a comparation field, with all column names in a collection “ALL_FIELDS” column 2) Second, we find match and unmatched KEY_ID3) Those KEY_ID that are in the new table, but not in reference table, were “INSERTED ONES”4) Those KEY_ID that are NOT in the new table, but are in reference table, were “DELETED ONES”5) Those KEY_ID that match both tables can be compared for changes on “ALL_FIELDS” columna. If all fields match in both tables, then “SAME ONES”b. If any field from reference table unmatches new table, them find the “DIFFERENTES ONES”Forum link: https://forum.knime.com/t/data-set-comparison/18507https://hub.knime.com/ipazin/spaces/Public/latest/2019_10_03_Comparing_Two_Tables~xU8aDokP89N2Y59xUpdated 2023-01-02 correction for (right) columns REFERENCETABLESEPARATENEW FROMOLDCOLLECTIONKEYCOLLECTIONKEYINSERTEDONESDELETEDONESSAMEKEY_IDSAMEONESDIFFERENTONESColumnsto includeSAMEKEY_IDNewTABLEMISSINGSTRINGMISSINGSTRINGINSERTEDDELETEDDIFFERENTSAMEonlycolumns Table Creator Joiner Create CollectionColumn Create CollectionColumn Column Filter Column Filter Joiner Column Filter CHANGES Columns Variables DuplicateRow Filter Table Creator Missing Value Missing Value Excel Writer 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: E314382
Created at: 2022-06-03
On NodePit since: 2022-06-06
Last update: 2024-04-28
Created with KNIME version: v4.7.0
Tags: comparetablesdifferencesinsertedchangeddeleted

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

Try NodePit Runner!