Compare multiple tables with N columns/fields

[Heinzomat]

Hi there,

one of our customers wants to compare multiple tables from 6 different tenants. These tables contain some base data and have the same columns. We would like to see, if there are any differences between the base data in these tables.

These tables have a multiple fields (most of them are more than 100+, so writing out every field name and comparing them between the tables for each tenant is not an option)

For example: 

Table A from tenant 1

Code	Value 1	Value 2	Value 3	…	Value N
1	Customer A	100	1234	…	ABC
2	Customer B	200	4868	…	ABC
3	Customer C	300	1689	…	ABC
4	Customer D	400	168	…	ABC
5	Customer  E	500	19879	…	ABC
…	…	…	…	…	…

Table A from tenant 2

Code	Value 1	Value 2	Value 3	…	Value N
1	Customer A	100	1234	…	ABC
2	Customer B	200	4868	…	ABC
3	Customer C	300	1689	…	ABC
4	Customer D	400	168	…	ABC
5	Customer  E	505	19879	…	ABC
…	…	…	…	…	…

Customer E has a difference in Value 2 (500 and  505).

The desired result would be a new table that looks like this:

Code	Value 1	Result
1	Customer A	Equal
2	Customer B	Equal
3	Customer C	Equal
4	Customer D	Equal
5	Customer  E	Different
…	…	…

I tried to solve this by iterating through the fields and compare them one by one. But that approach didnt work out so far.

Can someone help me? I appreciate any help!

Keynotes:

- The tables should contain the same data, we want to see if there are any differences

- The table columns share the same name!

- The tables have a lot of columns, manually writing a check for every column is not an option. The solution should be a loop or something similar

[Saravanan_Desingh · Accepted solution]

Are you looking something like this?

tents:
LOAD * INLINE [
    Code, Value 1, Value 2, Value 3, Value 4, Value N
    1, Customer A, 100, 1234, 1234, ABC
    2, Customer B, 200, 4868, 4868, ABC
    3, Customer C, 300, 1689, 1689, ABC
    4, Customer D, 400, 168, 168, ABC
    5, Customer E, 500, 19879, 19879, ABC
];

Concatenate(tents)
LOAD * INLINE [
    Code, Value 1, Value 2, Value 3, Value 4, Value N
    1, Customer A, 100, 1234, 1234, ABC
    2, Customer B, 200, 4868, 4868, ABC
    3, Customer C, 300, 1689, 1689, ABC
    4, Customer D, 400, 168, 168, ABC
    5, Customer E, 505, 19879, 19879, ABC
];

tenX:
CrossTable(Keys, Values)
LOAD  [Value 1]
	 ,[Value 2]
	 ,[Value 3]
	 ,[Value 4]
	 ,[Value N]
Resident tents;

tmpTents:
LOAD Code, [Value 1] Resident tents;

Left Join

LOAD * Resident tenX;

tabTents:
LOAD Code, [Value 1], Keys, Count(DISTINCT Values) As Count_Values
Resident tmpTents
Group By Code, [Value 1], Keys;

FinalTents:
LOAD Code, [Value 1], If(Count(DISTINCT Count_Values)<>1,'Different','Equal') As Result
Resident tabTents
Group By Code, [Value 1];

Drop Table tents, tenX, tmpTents, tabTents;

[Brett_Bleess]

If Saran's post helped, please be sure to close out your thread by using the Accept as Solution button on his post.  If you figured something else out, please consider posting that and then use the button to mark that post to close the thread.  It is appreciated if you do close your threads.

Regards,
Brett