topic Compare multiple tables with N columns/fields in QlikView

Compare multiple tables with N columns/fields

Heinzomat — Sat, 16 Nov 2024 00:53:47 GMT

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

Re: Compare multiple tables with N columns/fields

Saravanan_Desingh — Mon, 20 Apr 2020 02:06:02 GMT

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;

Re: Compare multiple tables with N columns/fields

Brett_Bleess — Wed, 29 Apr 2020 14:04:47 GMT

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