Comparing two data sets and return differences

[jillv]

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

[binuacs]

@jillv 

[jillv]

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.