15 Replies Latest reply: Sep 1, 2016 7:32 PM by Josephinet Tedesco RSS

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

    Don Lee

      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 1Header 2Header 3Header 4Header 5Header 6Header 7
      table 1


      table 2

      NumberStringExtra 1
      NumberStringExtra 2
      1A12
      2K6
      3B3
      3B3
      4C55
      7E76
      5D323



      7E76



       

       

      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?

          Hi!

           

          Plz find the attachment.

          Hope u find ur Solution.

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

              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?
                  John Witherspoon

                  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.

                   

                  HighlightDifferences.png

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

                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?
                  Mohammed Salman Shaikh

                  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?
                      John Witherspoon

                      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?
                          Mohammed Salman Shaikh

                          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?
                            Prem Prakash

                            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?
                          Sangram Reddy

                          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.