How can I do a reconciliation process on two data set

[Sshasnk]

Hi All,

I have two data set and I want to do a reconciliation process to check if both the data sets have same the value or different value.

Now, I know I can do it with formula too say TRUE or FALSE but I have 120+ fields to check so I don't want to write formula for all.

Example:

Dataset 1 

ID	name	source id 1	source id 2	source id 3	final id 1	final id 2
123	ABC	1	3	1	2	1
234	GHD	2			3	2
12	KLF	3	4		4	3
1	KNI	4	6	4	5	4
234	KNR	5	7	1	6	5

Dataset 2:

ID	name	source id 1	source id 2	source id 3	final id 1	final id 2
123	ABC	1	4	1	2	1
234	GH	2				2
12	KLF	3	1		4	3
1	KNI	4	5	4	3	4
234	KNR	5	7	1	6	5

Steps:

1. Join both the data set based on ID column 

2. Check if the dataset 1 data is there on dataset 2 or not. If dataset 1 value is present in data set 2 then true or false

Output

ID	name	source id 1	source id 2	source id 3	final id 1	final id 2	Right_ID	Right_name	Right_source id 1	Right_source id 2	Right_source id 3	Right_final id 1	Right_final id 2	Check_ID	Check_name	Check_source id 1	Check_source id 2	Check_source id 3	Check_final id 1	Check_final id 2
123	ABC	1	3	1	2	1	123	ABC	1	4	1	2	1	TRUE	TRUE	TRUE	FALSE	TRUE	TRUE	TRUE
234	GHD	2			3	2	234	GH	2				2	TRUE	FALSE	TRUE	TRUE	TRUE	FALSE	TRUE
12	KLF	3	4		4	3	12	KLF	3	1		4	3	TRUE	TRUE	TRUE	FALSE	TRUE	TRUE	TRUE
1	KNI	4	6	4	5	4	1	KNI	4	5	4	3	4	TRUE	TRUE	TRUE	FALSE	TRUE	FALSE	TRUE
234	KNR	5	7	1	6	5	234	KNR	5	7	1	6	5	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE

[Yoshiro_Fujimori]

@Sshasnk ,

If there is not a reason you stick to the expected output as above, I would suggest using Expect Equal tool in CReW macro.

https://marketplace.alteryx.com/en-US/apps/419776/crew-expect-equal

[aatalai]

you could use the exact equal method as @Yoshiro_Fujimori suggested assuming they are in the same order, another way could be transposing the data sets and joining on ID and Name, see workflow attached. @Sshasnk let me know how you get on.

[Raj]

\\deleted.

[Raj]

@Sshasnk 
one way of doing this
mark done if solved

[Ashish_TN_Singh]

Thank you for sharing, cheers !

[acotta17]

Hi,

You could approach this by first sorting both datasets and then using the Join tool on record. After joining, tag the values flowing from the left and right as "False," while the joined records would be marked as "True." 

Could someone let me know if this would work?