Hi, I am new in Alteryx and trying to figure out a workflow:
I have two data sets, I need to get the differences between the two data sets:
1. difference in amounts (but if the difference in value is only "1", it should NOT flag as a difference)
2. ID in file1 but missing in file2
3. ID in file2 but missing in file1.
My problem is the primary ID in File1 could be found in either of ID1 or ID2 columns of File2.
Appreciate any suggested solution on this!
Thanks in advance.
File1:
Primary ID | Amount |
IDTEST001 | 2298395 |
IDTEST002 | 86070 |
IDSAMP1 | 21540 |
IDSAMP2 | 85914 |
IDTEST003 | 120010 |
IDTEST004 | 1080 |
IDSAMP3 | 1344 |
IDSAMP5 | 3455 |
File2:
ID1 | ID2 | Amount |
IDTEST001 | IDTST01 | 2298395 |
IDTEST002 | IDTST02 | 86070 |
IDSAMP01 | IDSAMP1 | 21540 |
IDSAMP02 | IDSAMP2 | 85914 |
IDTEST003 | IDTST03 | 120011 |
IDTEST004 | IDTST04 | 1084 |
IDSAMP04 | IDSAMP4 | 2345 |
Expected Results flagging the differences:
ID | COMMENT |
IDTEST004 | Material difference |
IDSAMP3 | Missing in File 2 |
IDSAMP5 | Missing in File 2 |
IDSAMP4 | Missing in File 1 |
IDSAMP04 | Missing in File 1 |
Hello @binuacs, sorry i realized i am missing some information... I updated the original post to add more IDs in set2 table. Instead of having some fields empty in column ID1 and ID2, i populated it with IDs as well. Could you please try again using the updated table? Thank you and I appreciate your help.