I am trying to find a way to validate a source csv to a target csv to find :
1. records that are missing
2. records that match on key bust have different column values
It looked from the documentation that Table Validator Reference could perform the function but I am unable to create the required inputs for this to work. Is there a sample workflow for this component?
Mark P Ashworth
this is a bit tricky.The table validator only considers the spec (e.g. the column names and types). You however are interested in the values. (That's right?)
Add a constant value column to both tables (column name: Identifier, value: source for the source and target for the target).
Than concatenate both tables. Now group by everything, except the identifer column. This one set to concatenate in the option tab.
Rows having both values (source,target) are now the ones which are the same in source and target. Filter those.
Now, take the remaining ones, and group them again, but only by key. Use again the identifier column and set to comine.
In the output there now will be three possibilities for the identifier column
1. source (identifer was in source, and is not in target)
2. target (identifier was in target and is not in source)
3 source,target (identifier found in both, but some values are not equal.
I hope this helps.. .maybe someone know a simpler approach,