9 Replies Latest reply: Mar 28, 2017 2:47 PM by omar bensalem RSS

    Looking for matching records between two tables

    Qlik User

      Hi All,

       

      I'm currently trying to implement a solution to check records between 4 different bank statements (from different banks) and a financial master database statement. There is a lack of consistency between records however, and no standard unique reference number present in the reports produced by the master database, with records instead defined by a LineDescription field, e.g. 'SCOTT'S PIZZERIA DDR BGC'. The name in this LineDescription is the same as the name in the NAME field in the bank statements. Apart from this there is nothing linking the data tables.

       

      What I want to do is wildmatch the NAME from the bank statement tables with the LineDescription fields and have the records linked in this way.

       

      Is this possible? What is the best way of proceeding?

       

      Many Thanks,

      Qlik User

        • Re: Looking for matching records between two tables
          Muñoz Héctor

          Hi Qlik User,

          If I have not missunderstood what you are asking is to join tables among text fields... This is possible but it is recommendable to do by number. For this, you can use Autonumberhash functions.

          Regards,

          H

            • Re: Looking for matching records between two tables
              Qlik User

              Hi Munoz,

               

              I think you're thinking along the right lines. Let me give an example of such a record.


              Financial Master Database Statement Table:

              LineDescription = 'SCOTT'S PIZZERIA BGC 53042043 24/02'

               

              Bank Statement Table:

              NAME='SCOTT'S PIZZERIA'

               

              The contents of the NAME field will always be included in the LineDescription with no anomalies, as the bank statements feed this main system. I want to flag it where the value is present in more than one table.

               

              Kind Regards,

              Qlik User

                • Re: Looking for matching records between two tables
                  Muñoz Héctor

                  How many records are in each table?

                    • Re: Looking for matching records between two tables
                      Qlik User

                      Not a huge volume. The maximum that will occur in a month is around 4000 in the master table and 1000 in each of the bank statement tables.

                        • Re: Looking for matching records between two tables
                          Muñoz Héctor

                          Hi Qlik User,

                           

                          I attach a sample with some joins that will make something similar than what you need.

                           

                          In this example I have two tables:

                           

                          T1 with footbal teams

                          T2 with footbal players

                           

                          28-03-2017 17-40-24.png

                           

                          They share a field Description that is similar but not exactly the same. I have make a join to T1 using the first word in T2 Description and later I have cleared the result. Th precondition is that T2 Description is a left substring of T1 description.

                           

                          Hope it serves...

                           

                          Regards,

                          H

                            • Re: Looking for matching records between two tables
                              Qlik User

                              Thanks Munoz, this looks like what I'm looking for - I don't however have a commercial version of QlikView and have ran out of the number of applications I can open. Are you able to produce as a .qvf or similarly send the sample data along with the load script?

                               

                              Thoroughly appreciate the time and effort, and love the example!

                                • Re: Looking for matching records between two tables
                                  Muñoz Héctor

                                  This is the script:

                                   

                                  T1_AUX:

                                  LOAD * INLINE [

                                  T1 ID, T1 Description

                                  1, Barcelona FC

                                  2, Real Madrid Club de Futbol

                                  3, Real Betis Balompie

                                  4, Atletico de Madrid

                                  5, Athletic de Bilbao

                                  ];

                                   

                                  T1_AUX2:

                                  NOCONCATENATE LOAD [T1 ID] AS [T1 ID],

                                    [T1 Description] AS [T1 Description],

                                    SubField([T1 Description], ' ', 1) AS [T1 Key]

                                  RESIDENT T1_AUX;

                                   

                                  DROP TABLE T1_AUX;

                                   

                                  T2_AUX:

                                  LOAD * INLINE [

                                  T2 ID, T2 Description, T2 Player

                                  1, Barcelona, Messi

                                  2, Barcelona, Neymar

                                  3, Barcelona, Suarez

                                  4, Barcelona, Iniesta

                                  5, Real Madrid, Bale

                                  6, Real Madrid, Ronaldo

                                  7, Real Madrid, Isco

                                  8, Real Betis, Joaquin

                                  9, Real Betis, Martinez

                                  10, Real Betis, Perez

                                  11, Real Betis, Moya

                                  12, Real Betis, Lopez

                                  13, Real Betis, Antunez

                                  14, Atletico de Madrid, Koke

                                  15, Atletico de Madrid, Gabi

                                  16, Atletico de Madrid, Filipe Luis

                                  17, Atletico de Madrid, Oblak

                                  18, Atletico de Madrid, Godin

                                  19, Athletic de Bilbao, Yeray

                                  20, Athletic de Bilbao, Aduriz

                                  ];

                                   

                                  T2:

                                  NOCONCATENATE LOAD [T2 ID] AS [T2 ID],

                                    [T2 Description] AS [T2 Description],

                                    [T2 Player] AS [T2 Player],

                                    SubField([T2 Description], ' ', 1) AS [T2 Key]

                                  RESIDENT T2_AUX;

                                   

                                  DROP TABLE T2_AUX;

                                   

                                  LEFT JOIN (T1_AUX2)

                                  LOAD [T2 Key] AS [T1 Key],

                                    [T2 Description] AS [T1 Temp Description]

                                  RESIDENT T2;

                                   

                                  T1:

                                  NOCONCATENATE LOAD [T1 ID] AS [T1 ID],

                                    [T1 Description] AS [T1 Description],

                                    [T1 Temp Description] AS [T2 Description]

                                  RESIDENT T1_AUX2

                                  WHERE SubStringCount([T1 Description], [T1 Temp Description]) > 0;

                                   

                                  DROP TABLE T1_AUX2;

                                   

                                   

                                  Regards,

                                  H

                      • Re: Looking for matching records between two tables
                        Mark Munich

                        If i were in you situation, I would try to cleanse the data first and save into a QVD file before proceeding further. this would make your task easier. Though i didnot clearly understand your problem. incase if you have common fields in tables and want to check the matching records, as per qlikview there needs to be a atleast a common field between the tables.

                        • Re: Looking for matching records between two tables
                          omar bensalem

                          Here's how I did:

                           

                          "Financial Master Database Statement Table":


                          load *, SubField(LineDescription,' ',1)&SubField(LineDescription,' ',2) as key;


                          Load * Inline [

                          LineDescription

                          SCOTTS PIZZERIA BGC 53042043 24/02

                          SCOTTS PIZZERIA BGC 53042043 24/03

                          SCOTTS PIZZERIA BGC 53042043 24/04

                          Omar Ben Salem et compagnie

                          Omar Ben Salem et qlik

                          ];

                           

                           

                          "Bank Statement Table":


                          load *, SubField(NAME,' ',1)&SubField(NAME,' ',2) as key;


                          load * Inline [

                          NAME

                          SCOTTS PIZZERIA

                          Omar Ben Salem

                          ];

                           

                          Result:

                           

                          Capture.PNG