We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Comparing two data sets and return differences

jillv
6 - Meteoroid

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 IDAmount
IDTEST0012298395
IDTEST00286070
IDSAMP121540
IDSAMP285914
IDTEST003120010
IDTEST0041080
IDSAMP31344
IDSAMP53455

 

File2: 

ID1ID2Amount
IDTEST001IDTST012298395
IDTEST002IDTST0286070
IDSAMP01IDSAMP121540
IDSAMP02IDSAMP285914
IDTEST003IDTST03120011
IDTEST004IDTST041084
IDSAMP04IDSAMP42345

 

 

Expected Results flagging the differences:

IDCOMMENT
IDTEST004Material difference
IDSAMP3Missing in File 2
IDSAMP5Missing in File 2
IDSAMP4Missing in File 1
IDSAMP04Missing in File 1
2 REPLIES 2
binuacs
17 - Castor

@jillv 

binuacs_0-1646809857260.png

 

jillv
6 - Meteoroid

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.  

Labels