Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Heinzomat
Partner - Contributor
Partner - Contributor

Compare multiple tables with N columns/fields

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

CodeValue 1Value 2Value 3Value N
1Customer A1001234ABC
2Customer B2004868ABC
3Customer C3001689ABC
4Customer D400168ABC
5Customer  E50019879ABC

 

Table A from tenant 2

CodeValue 1Value 2Value 3Value N
1Customer A1001234ABC
2Customer B2004868ABC
3Customer C3001689ABC
4Customer D400168ABC
5Customer  E50519879ABC

 

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

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

CodeValue 1Result
1Customer AEqual
2Customer BEqual
3Customer CEqual
4Customer DEqual
5Customer  EDifferent

 

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

Labels (3)
1 Solution

Accepted Solutions
Saravanan_Desingh

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;

View solution in original post

2 Replies
Saravanan_Desingh

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
Employee
Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.