8 Replies Latest reply: Nov 28, 2012 2:33 PM by Jaime Marines RSS

    Matching two Pivot tables

      Hello Qlik Community;

       

       

      I have 2 Large Pivot Tables with One Key field (remision) on both, then I need to match these Tables and prompt the results with match or unmatching results.......Is it possible? .......

       

       

      Thanks a lot!

        • Re: Matching two Pivot tables
          Vlad Gutkovsky

          Unfortunately that's not possible. You will need to copy the expression(s) from one table to the other one and use it/them to compare the results.

           

          Regards,

          Vlad

            • Re: Matching two Pivot tables
              Jaime Marines

              Hi; I received below message trying to match one field:

               

              Field names must be unique in concatenate table...Load ,'Table2' as Table,

                                                                                                  remision,idestacion,vnatural

               

              My script:

               

              Directory;

              TablaA:

              ,'Table 1' as Table,

              REMISION as remision,

              AEROPUERTO as idestacion,

              LITROS as vnatural

              FROM

              [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\PedidosVentas1Jun2010.qvd](qvd);

               

              INNER JOIN (Table)

              LOAD*

              WHERE "Only in one table?"

              OR "idestacion Different?"
              OR "vnatural Different?"
              ;
              LOAD

               

               

              remision

               

              ,if (count (remision) <2,-1) as "Only in one Table?"

              ,if(maxstring(idestacion)<>minstring(idestacion),-1) as "idestacion Different?"
              ,
              if(max(vnatural)<>min(vnatural),-1) as "vnatural Different?"
              RESIDENT Table
              GROUP BY remision;

              STORE Table INTO C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\ESLK1JJOINA.qvd;

               

              LOAD*
              ,'Table 2'
              as Table,
              remision,
              idestacion,
              vnatural
              FROM
              [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\Movs1Jun2010.qvd](
              qvd);

               

              CONCATENATE

              • Re: Matching two Pivot tables
                Jaime Marines

                Hi; I received below message trying to match one field:

                 

                Field names must be unique in concatenate table...Load ,'Table2' as Table,

                                                                                                    remision,idestacion,vnatural

                 

                My script:

                 

                Directory;

                TablaA:

                ,'Table 1' as Table,

                REMISION as remision,

                AEROPUERTO as idestacion,

                LITROS as vnatural

                FROM

                [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\PedidosVentas1Jun2010.qvd](qvd);

                 

                INNER JOIN (Table)

                LOAD*

                WHERE "Only in one table?"

                OR "idestacion Different?"
                OR "vnatural Different?"
                ;
                LOAD

                 

                 

                remision

                 

                ,if (count (remision) <2,-1) as "Only in one Table?"

                ,if(maxstring(idestacion)<>minstring(idestacion),-1) as "idestacion Different?"
                ,
                if(max(vnatural)<>min(vnatural),-1) as "vnatural Different?"
                RESIDENT Table
                GROUP BY remision;

                STORE Table INTO C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\ESLK1JJOINA.qvd;

                 

                LOAD*
                ,'Table 2'
                as Table,
                remision,
                idestacion,
                vnatural
                FROM
                [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\Movs1Jun2010.qvd](
                qvd);

                 

                CONCATENATE

                • Re: Matching two Pivot tables
                  Jaime Marines

                  Hi; I received below message trying to match one field:

                   

                  Field names must be unique in concatenate table...Load ,'Table2' as Table,

                                                                                                      remision,idestacion,vnatural

                   

                  My script:

                   

                  Directory;

                  TablaA:

                  ,'Table 1' as Table,

                  REMISION as remision,

                  AEROPUERTO as idestacion,

                  LITROS as vnatural

                  FROM

                  [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\PedidosVentas1Jun2010.qvd](qvd);

                   

                  INNER JOIN (Table)

                  LOAD*

                  WHERE "Only in one table?"

                  OR "idestacion Different?"
                  OR "vnatural Different?"
                  ;
                  LOAD

                   

                   

                  remision

                   

                  ,if (count (remision) <2,-1) as "Only in one Table?"

                  ,if(maxstring(idestacion)<>minstring(idestacion),-1) as "idestacion Different?"
                  ,
                  if(max(vnatural)<>min(vnatural),-1) as "vnatural Different?"
                  RESIDENT Table
                  GROUP BY remision;

                  STORE Table INTO C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\ESLK1JJOINA.qvd;

                   

                  LOAD*
                  ,'Table 2'
                  as Table,
                  remision,
                  idestacion,
                  vnatural
                  FROM
                  [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\Movs1Jun2010.qvd](
                  qvd);

                   

                  CONCATENATE

                    • Re: Matching two Pivot tables
                      Vlad Gutkovsky

                      James,

                      I think I misunderstood you. I thought that by "pivot table" you meant a QV pivot table, not a pivot table in a data source. Something like this could probably work for you:

                       

                       

                      TablaA:
                      LOAD
                         'Table 1' as Table,
                         REMISION as remision,
                         AEROPUERTO as idestacion,
                         LITROS as vnatural
                      FROM
                      [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\PedidosVentas1Jun2010.qvd] (qvd); 
                      
                      CONCATENATE LOAD
                         'Table 1' as Table,
                         REMISION as remision,
                         AEROPUERTO as idestacion,
                         LITROS as vnatural
                      FROM
                      [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\Movs1Jun2010.qvd] (qvd); 
                       
                      LEFT JOIN LOAD
                         *,
                         if (count (remision) <2,
                            'Only in one Table?',
                            if(maxstring(idestacion)<>minstring(idestacion),
                               'idestacion Different?',
                               if(max(vnatural)<>min(vnatural)
                                  'vnatural Different?',
                                  'Match'
                               )
                            )
                         ) as Comparison
                      RESIDENT TablaA
                      GROUP BY remision
                      ;
                      
                        • Re: Matching two Pivot tables
                          Jaime Marines
                          
                          

                           

                          Hello;

                           

                          Thanks a lot! Below my last Script. It works.

                           

                          SET ThousandSep=',';
                          SET DecimalSep='.';
                          SET MoneyThousandSep=',';
                          SET MoneyDecimalSep='.';
                          SET MoneyFormat='$#,##0.00;-$#,##0.00';
                          SET TimeFormat='hh:mm:ss TT';
                          SET DateFormat='DD/MM/YYYY';
                          SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
                          SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
                          SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

                          Directory;
                          TablaA:
                          LOAD
                          //,'Table 1' as Table,
                              REMISION as remision,
                              AEROPUERTO as idestacion,
                              LITROS as vnatural,
                              Year,
                              Month,
                              Day
                          FROM
                          [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\PedidosVentas1Jun2010.qvd](qvd);

                          CONCATENATE

                          //***************MOVIMIENTO TABLE******************

                          LOAD
                          //,'Table 2' as Table,
                              idmovimiento,
                              tmovimiento,
                              mtipo,
                              remision,
                              idestacion,
                              vnatural,
                              Year,
                              Month,
                              Day
                          FROM
                          [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\Movs1Jun2010.qvd](qvd);

                          //******************INNER JOIN*************************
                          INNER JOIN (TablaA)
                          LOAD*
                          WHERE "No Match Tmov=S"
                            OR  "No Match Tmov =E,M"
                            OR  "Si Match idestacion Different"
                            OR  "vnatural Different?"
                          ;
                          LOAD
                          remision
                          ,if(count(remision)<2 ,-1) as "No Match Tmov=S"
                          ,if(count(remision)<2 ,-2) as "No Match Tmov =E,M"
                          ,if(maxstring(idestacion)<>minstring(idestacion),-3) as "Si Match idestacion Different"
                          ,if(max(vnatural)<>min(vnatural),-4) as "vnatural Different?"
                          RESIDENT TablaA
                          GROUP BY remision;

                          STORE TablaA INTO C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\ESLK1JJOINA.qvd; 

                           

                          Best Regards

                           

                          Jaime