Hi,
This is my output:
Team | City | Deposits | Right_Team | Right_City | Right_Deposits | Result |
Raptors | Montreal | 5000000 | Raptors | Montreal | 5000000 | Matched |
Canadians | Montreal | 100000 | Data 1 Unmatched in Data 2 | |||
Leafs | Toronto | 200000 | Data 1 Unmatched in Data 2 | |||
Raptors | Toronto | 100000 | Data 1 Unmatched in Data 2 | |||
Raptors | Toronto | 10 | Data 2 Unmatched in Data 1 | |||
Leafs | Toronto | 2300000 | Data 2 Unmatched in Data 1 | |||
Canadians | Montreal | 1500000 | Data 2 Unmatched in Data 1 |
As you can see it compares like columns.
However in my rsult formula, is it possible to switch it from "Data 1 Unmatched in Data 2" to something like
Row 2 result would be - Data 1 unmatched in Data 2: Team, City, Deposits.
COuld this be done?
Solved! Go to Solution.
convert to string. add a record id. transpose - use recordid as a key field. use a formula tool to extract if [Name] contains "Right" or not - call it "source" (or something). group by recordid/source/concatenate [Value] with no delimiter. join on recordid. compare if the two concatenated values are equal. if they are "Matched" if one source is empty - then you can build your logic etc... join this new field back to your original data on recordid.
If you want to add where specifically the [Name] field doesn't match you'd do the same kind of transpose. You'd remove the "Right_" suffix from the data2 fieldnames. Then you'd summarize group by record id/name and count distinct values in value. filter where the distinct value is not 1 and then concatenate the name field by record id - this would tell you which fields in each records don't match.
@apathetichell anychance you can recreate the workflow?
@nagakavyasri thanks, the only issue is the the columns loook to be hardcoded here -
IF Contains([Result], "unmatched") THEN [Result]+":"+[Team]+","+[City]+","+[Deposits] ELSE [Result] ENDIF