7 Replies Latest reply: May 11, 2017 2:58 PM by Wallo Atkinson RSS

    Matching values of 2 tables

    Eduardo DImperio

      Hi,

       

      I need to check if exists common data between two tables. For that i try 2 ways

       

      //***************Using Inner Join

       

      RS_BILLING:

      Load

      OID_SYSTEM

      From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);

       

       

      Inner Join(RS_BILLING)

       

      LOAD

          OID_SYSTEM,

          OID_GROUP,

       

      FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]

      (qvd)

      WHERE OID_GROUP = 20 OR OID_GROUP = 27;

       

      Output:

      Load

      Isnull(OID_SYSTEM) AS Check

      resident (RS_BILLING);

       

      //***************** Using Exists()

       

      RS_BILLING:

      Load

      OID_SYSTEM

      From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);

       

      SISTEMA:

      LOAD

        OID_SYSTEM,

          OID_GROUP,

          If(Exists(OID_SYSTEM,), 'Yes', 'No') AS CHECK

      FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]

      (qvd)

      WHERE OID_GROUP = 20 OR OID_GROUP = 27;

       

      In both cases the right output is Null, but i receive missing values. For that reason Isnull and other tools to work if Nulls doesn't work.

       

      Any idea?

        • Re: Matching values of 2 tables
          Wallo Atkinson

          Try ApplyMap

            • Re: Matching values of 2 tables
              Eduardo DImperio

              Hi Wallo, i already tried to and it not work,to use that i neet at least 3 fields in a table (that was the error message).

              To be honest, i look again my code and realize that was inverted the tables, first is:

               

               

              SISTEMA:

              LOAD

                OID_SYSTEM,

                  OID_GROUP

               

               

              FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]

              (qvd)

              WHERE OID_GROUP = 20 OR OID_GROUP = 27;

               

              and after

               

              RS_BILLING:

              Load

              OID_SYSTEM,

              If(Exists(OID_SYSTEM,), 'Yes', 'No') AS CHECK

              From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);

               

              But still not working cause i receive to the same OID_SYSTEM  YES AND NO. Its duplicated and i dont know why.

               

              Exists1.JPG

                • Re: Matching values of 2 tables
                  Wallo Atkinson

                  It's probably doing an autoconcatenate.

                   

                  But if you go with a join, I think you'd want a left join,

                  RS_BILLING:

                  Load

                  OID_SYSTEM

                  From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);

                   

                   

                  LEFTJoin(RS_BILLING)

                   

                  LOAD

                      OID_SYSTEM,

                      OID_GROUP,

                   

                  FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]

                  (qvd)

                  WHERE OID_GROUP = 20 OR OID_GROUP = 27;

                   

                  Output:

                  Load

                  if(Isnull(OID_GROUP),'No','Yes') AS Check

                  resident (RS_BILLING);

                    • Re: Matching values of 2 tables
                      Eduardo DImperio

                      I dont know if im doing this. How can i check?

                      About join not working too, but left join doesnt give me the intersection anyway between two tables.

                        • Re: Matching values of 2 tables
                          Wallo Atkinson

                          What is the key field or fields between the 2 tables?  OID_SYSTEM?

                          Not sure I'm understanding what you want.  Maybe this?

                           

                          List:

                          Load

                          OID_SYSTEM

                          From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);

                           

                           

                          concatenate(List)

                           

                          LOAD distinct

                              OID_SYSTEM

                           

                          FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]

                          (qvd)

                          WHERE OID_GROUP = 20 OR OID_GROUP = 27;

                           

                          MapBillingLd:

                          OID_SYSTEM,

                          'Yes' as Match

                          From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);

                           

                          MapRSLd:

                          LOAD

                              OID_SYSTEM

                          'Yes' as Match

                          FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]

                          (qvd)

                          WHERE OID_GROUP = 20 OR OID_GROUP = 27;

                           

                          noconcatenate

                           

                          Final:

                          load *,

                          applymap('MapBillingLd',OID_SYSTEM,'No') as BillingTableMatch,

                          applymap('MapRSLd',OID_SYSTEM,'No') as RSTableMatch

                          resident List;

                  • Re: Matching values of 2 tables
                    Eduardo DImperio

                    Wallo, I solved.

                     

                    Its similar to your post.

                     

                    SISTEMA:

                    LOAD

                      OID_SYSTEM,

                        OID_GROUP

                     

                    FROM [lib://Dados/MI\RS\RS_SYSTEM.qvd]

                    (qvd)

                    WHERE OID_GROUP = 20 OR OID_GROUP = 27;

                     

                    CONCATENATE

                     

                    Load

                    OID_SYSTEM

                    From [lib://Dados/MI/Leituras/Fechamento/SABESP_RS_BILLING.qvd](QVD);

                     

                    LEFT JOIN(SISTEMA)

                     

                    Output:

                    Load

                    OID_SYSTEM,

                    if(Isnull(OID_GROUP),'No','Yes') AS Check

                    resident SISTEMA;

                     

                    The diference its need to concatenate the two tables first and then use a left join on this new table. Thanks for the help !