Hi. I would like to be able to loop through each column within two joined tables and compare the values in each row. For example, I have used the Joiner node to join the following two tables using Employee ID as the match.
Is it possible to loop through each column of the two tables to compare the values in all of the rows. I have done this using the column comparator node for each of the columns but some of the data tables that I will be comparing will have more columns to compare.
The expected output would be an Excel file for each column showing the mis-matches.
I have tried this, but it does not give me the results I am after. In my actual data sets, there are some records that are in table 1 that are not in table 2 (and vice versa). This is why I have used the Joiner node so that it gives me the three results (join, left unmatched, right unmatched).
I then have to compare each of the columns in the join (matched) table to see where there are data mismatches (e.g., same employee ID but with different names).
Could I use a column splitter to separate out the joined tables and then the table difference finder node?
Secondly, use a column expressions node and create one column per rule:
if column first name on left table == column first name second table, then “Ok”, else “Different”
if column last name on left table == column last name second table, then “Ok”, else “Different”
same for all other columns
Then, you can filter rows accoridng to the problem you want to target using a row filter node (example: select all rows having a mismatch on “person type”) and eventually even export it.
How would I enter the expression for each column when I configure the node? Could you give me an example of the syntax to use within the configure dialog box?
