topic Re: How to compare 2 tables and put non-matching results? in QlikView

How to compare 2 tables and put non-matching results?

Thu, 04 Aug 2011 00:56:19 GMT

Hello,

I am really new to the community and really new to 'coding' I have basic comcept of programing (pascal) and that's about it.

I find QlikView very intuitive and brilliant and want to learn more about it.

I would like to compare the 2 tables in excel spreadsheet but pull the records that does not match each according to primary key.

Header 1	Header 2	Header 3	Header 5	Header 6	Header 7
table 1			table 2
Number	String	Extra 1	Number	String	Extra 2
1	A	12	2	K	6
3	B	3	3	B	3
4	C	55	7	E	76
5	D	323
7	E	76

in above table, I want to compare the table 1 and 2 by column 'Number' and pull the rows that does not match.

Can please someone help me?

Thank you!

Re: How to compare 2 tables and put non-matching results?

Thu, 04 Aug 2011 04:14:27 GMT

Hi!

Plz find the attachment.

Hope u find ur Solution.

Re: How to compare 2 tables and put non-matching results?

Thu, 04 Aug 2011 20:53:02 GMT

Erika,

Thank you very much for posting the possible answer for me but unfortunately, I am running personal edition of QlikView and I cannot open your attached file. If it is not too much, will you be able to put it on txt file with brief explanation?

Thank you for your help in advance.

Re: How to compare 2 tables and put non-matching results?

johnw — Thu, 04 Aug 2011 21:25:18 GMT

OK, here's an example script doing similar to what you're looking for:

Table:
LOAD *
,'Table 1' as Table
INLINE [
OrderItem, Customer, Amount
OI1, Ann, 500
OI2, Bob, 600
OI3, Charlie, 2000
OI4, Dennis, 200
OI6, Fred, 500
];
CONCATENATE
LOAD *
,'Table 2' as Table
INLINE [
OrderItem, Customer, Amount
OI2, Bobby, 600
OI3, Charlie, 200
OI4, Dennis, 200
OI5, Erin, 500
OI6, Fred, 500
];
INNER JOIN (Table)
LOAD *
WHERE "Only in One Table?"
OR "Customer Different?"
OR "Amount Different?"
;
LOAD
OrderItem
,if(count(OrderItem)<2,-1) as "Only in One Table?"
,if(maxstring(Customer)<>minstring(Customer),-1) as "Customer Different?"
,if(max(Amount)<>min(Amount),-1) as "Amount Different?"
RESIDENT Table
GROUP BY OrderItem
;

Both source tables are concatenated into the same final table, just with a "Table" field identifying the source. Then we load another table keyed and grouped by order item, the key we want to compare rows for. Since we're grouping, we can compare the values from both tables with aggregation expressions like min() and max(). Finally we inner join this new table back to the first, though with a preceding load to save time and space. The inner join eliminates any rows that don't have any differences. You could keep them if you wanted and set an overall "Row Different?" flag using the current WHERE logic for this inner join.

Now in the application, we can calculate some things:

Number of Records with Differences = count(distinct OrderItem)
Records in Only One Table = count("Only in One Table?")
Customer Differences = count("Customer Different?")/2
Amount Differences = count("Amount Different?")/2

We can also make a chart that highlights the differences. Make a straight table with OrderItem, Table, Customer and Amount as dimensions. No expressions, and remove the checkmark that suppresses zero values. Now set a background color expression for every dimension:

OrderItem = if("Only in One Table?",lightgray())
Table = if("Only in One Table?",lightgray())
Customer = if("Customer Different?",lightgray())
Amount = if("Amount Different?",lightgray())

Edit: The chart looks like this. If the record is only in one table, the key and the table are highlighted. If in both tables, all field differences are highlighted.

How to compare 2 tables and put non-matching results?

rahulgupta — Sat, 06 Aug 2011 17:27:43 GMT

Try this:

Table1:

Load

Number,

Number as Number1,

string as string1,

Extra1

from Table1.qvd;

Table2:

Load

Number,

Number as Number2,

string as string2,

Extra2

from Table2.qvd;

After loading it,take a Straight Table.

Dimension:Number

Expression:if(Number=Number1 and Number=Number2,'match','Nomatch')

Re: How to compare 2 tables and put non-matching results?

Wed, 10 Aug 2011 23:51:06 GMT

Rahul,

Thank you for the reply, however your solution were not able to bring the information that were not matching from both tables.

Re: How to compare 2 tables and put non-matching results?

Wed, 10 Aug 2011 23:53:34 GMT

John,

I am sorry for the very late reply and very thank you for the answer that I was looking for.

Re: How to compare 2 tables and put non-matching results?

qlikviewforum — Sun, 20 Apr 2014 17:42:01 GMT

Hi John,

Can you please help me out in below post. Basically I would like to highlight what is new in new hierarchy. It would be really great if you can help me out here.

Compare the 2 list boxes which contain hierarchy

Re: How to compare 2 tables and put non-matching results?

Fri, 01 Apr 2016 15:30:40 GMT

Why do I need to use inline function? How are you supposed to know the fields before hand and what if the table is updated in the future. I am tired of googling out to find a proper solution to this. Sorry to be harsh but I feel its so much complex in Qlikview I know the logic in SQL for this but due to the limitations of Qlikview I cannot find a proper solution.

And your solution is difficult to understand since you haven't taken the names of Field same as those to what the Question has. A proper solution to this without using the INLINE would be really helpful. Thanks in advance.

Re: How to compare 2 tables and put non-matching results?

johnw — Fri, 01 Apr 2016 17:39:27 GMT

It seems like you want a much broader solution, not "I want to compare this specific table and these specific fields", but "I want an app where I can just give it two data sources to compare, tell it the key field or fields, and it loads all fields from both data sources, then builds a chart that highlights the differences." It also seems like you think that's the only proper solution, and that I owe it to you to code the app for you.

I understand your frustration, but I'm not your employee, I didn't cause your problem, and being harsh with me is counter-productive. Nobody owes you a solution. Anyone here who might help you is just someone like you that happens to like to help people in their spare time.

That said, this DOES sound like a very interesting challenge, and I think I know how I'd approach it. For the script, there are functions that let you identify the fields in a table. I'm sure there's a way to loop through those fields and build the necessary data structures. For the chart, I'm sure there's a way to write a macro to add all the fields and do the highlighting. But it would quite a bit more complicated than the solution I posted above, even if it vaguely resembled it.

The first thing I'd do is search around the Internet to see if someone else already wrote such an app. You say you're tired of googling, so maybe it doesn't exist, but I'd check robwunderlich.com for the QlikView Cookbook and QlikView Components. I've worked with Rob, and I am unashamed to say that he is a better QlikView expert than I'll ever be. If not there, I'd be looking around the blogs of other experts, and searching through the forum of course.

But I'm guessing there's no such app. I'm guessing it would take me three to five days to code such an app. And I just don't have that kind of time to donate. I have a job. I'm behind in the projects they pay me for. But if someone has time to donate, and these hints are enough to put them on the right path, or they have better ideas, it might be a nice app for the Qlik community to have.

Some things QlikView is good at. Some things require complex solutions that push the boundaries of its capabilities. This seems to be an example of the latter - QlikView was just not designed to compare two tables and tell you the non-matching results. That just isn't its function, not its strength. I'm sorry if you find that frustrating, but you're trying to hammer a nail with a screwdriver - you can do it, but there's probably a better way. Perhaps there's a better tool out there for this need than QlikView.

Re: How to compare 2 tables and put non-matching results?

Fri, 01 Apr 2016 17:54:01 GMT

You read my mind! As I said before I am sorry to honest but yes you are exactly right that Qlikview isn't designed to make such comparisons. I am familiar of JAVA and SQL Server and this tool is totally new thing for me. Like JAVA or any other programming language, Qlikview cannot be bent as and how you want sadly. I am currently on the job training for Qlikview only and Qlikview 11 for Developers has really helped me know how the tool works in a very short time. I really like the associativity power of this tool to make very fast analysis unlike other tools which use a drill down approach but sadly I was accustomed to programming in Java and likewise and at times I feel weak when I cannot accomplish things in a simple way. And another thing is finding a proper solution is difficult for this tool since there are not so many people as there are on Stack Overflow which I used to solve any programming doubts. But thanks for making it clear to me. As a fresher in this tool, I want to move fast but I guess I'll have to rattle the cage of my mind to find those answers or leave those questions unanswered. Again sorry if was being harsh or rude

Re: How to compare 2 tables and put non-matching results?

Anonymous — Fri, 17 Jun 2016 13:10:26 GMT

Hi John,

I am great admirer of your solutions in Qlikview and QlikSense.

I also have some similar requirements where I need to build a solution of data comparison from 2 different systems. Its basically a data migration from 2 systems. The data from system A getting updated in System B. The extract from System A extract will be delta on daily basis and need to be compared with System B data which need to be in Sync. There are chances that multiple fields may have wrongly updated or missed during the course of migration to system B. I need to develop an app which compare the data mismatch from Stem B wrt to system A. There may be chances that 100 records sent from Sys A and only 70 records getting updated. And next day after the data fix, there may 2nd day data plus the fixed data gets updated in System B.

Can you please suggest the best approach which can help me getting this developed in QlikSense. I believe, if its the same number of records and same records gets into both system with unique transaction id, we can compare one to one in same row. But id there is missing records or more records, I doubt over the solution.

Please help.

Re: How to compare 2 tables and put non-matching results?

johnw — Mon, 20 Jun 2016 16:27:39 GMT

Well, the solution here does highlight rows missing from one or the other table, but I don't know if this sort of script and table works in QlikSense. If not, I won't be much help, as I've not played around with QlikSense.

Re: How to compare 2 tables and put non-matching results?

reddy-s — Tue, 21 Jun 2016 12:15:52 GMT

The best way to do this would be to load the data from both the sources in to one application (QVF/QVW) and have a field to differentiate data from both sources. In this way you will be able to leverage the power of the Qlik associative engine and also find the missing information you are looking for simultaneously.

Thanks,

Sangram.

Re: How to compare 2 tables and put non-matching results?

Tue, 28 Jun 2016 11:32:12 GMT

Hello

I need same, but using NOT two separate files, but merged in one cube using load with * : C:\Users\...\orders_*.xlsx

so how to mark PO numbers not matching = POs added in file in 0628?

Re: How to compare 2 tables and put non-matching results?

josephinetedesc — Thu, 01 Sep 2016 23:32:14 GMT

Thank you John Witherspoon - this is exactly what i was looking for!

This would be really good as a tutorial

Re: How to compare 2 tables and put non-matching results?

swiftfahad — Thu, 06 Sep 2018 04:04:40 GMT

What if I have long table with lots of data loading from Excel file.. what would be the script than?

Re: How to compare 2 tables and put non-matching results?

Drewster — Mon, 14 Sep 2020 07:12:36 GMT

Hi johnw,

i have found your comparing from 2 different qvd useful, however i have a use case where i have to retain the data after comparing.
https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-retain-data-from-a-previous-load-to-show-trends/td-p/139243

it is pretty similar to this link above. Please do let me know if there are any work around for this use case. Thanks!