5 Replies Latest reply: Apr 9, 2013 9:24 AM by Narender Makhija RSS

    Script to join 3 tables in one

    Ankri Laurent

      Hi

       

      I have 3 tables :

       

      QV_RET_EMPLOYES.QVD

      Columns :

      NUM_OVED

      YEAR_COMMERCIAL

      HEURES_VALIDEES,

       

      QV_RET_OVED_VW.QVD

      Columns :

      NUM_OVED

      GROUPE_OVED

      COMMERICAL_NUM


      QV_PAIES.QVD

      NUM_OVED

      YEAR_CONTACT

      SALAIRE_BRUT_TOTAL

       

      I want to write a script that concatenate the 3 tables in one table (the bases is QV_PAIES.QVD) and I want in final the columns :

      NUM_OVED

      YEAR_FAMILLE (YEAR_CONTACT = YEAR_COMMERCIAL)

      SALAIRE_BRUT_TOTAL

      GROUPE_OVED

      COMMERCIAL_NUM

      HEURES_VALIDEES

       

      I have tried this but it doesn't work :

       

      QV_RET_EMPLOYES :

      LOAD

      NUM_OVED,

      YEAR_COMMERCIAL as YEAR_FAMILLE,

      HEURES_VALIDEES

      FROM

      \\As01\qlikview\loader\QV_RET_EMPLOYES.QVD

      (qvd);

       

       

      Join

       

      QV_RET_OVED_VW :

      LOAD

      NUM_OVED,

      GROUPE_OVED,

      COMMERCIAL_NUM

      FROM

      \\As01\qlikview\loader\QV_RET_OVED_VW.QVD

      (qvd);

       

       

      Join

       

       

      QV_PAIES_OVED:

      LOAD

      NUM_OVED,

      YEAR_CONTACT as YEAR_FAMILLE,

      SALAIRE_BRUT_TOTAL

      FROM \\As01\qlikview\loader\QV_PAIES.QVD

      (qvd);

       

       

      QV_PAIES_FINAL:

      LOAD

           NUM_OVED,

         YEAR_FAMILLE,

                SALAIRE_BRUT_TOTAL,

                 GROUPE_OVED,

                COMMERCIAL_NUM,

                HEURES_VALIDEES

      Resident QV_RET_OVED_VW;

       

       

      Drop Table QV_RET_OVED_VW

       

      Drop Table QV_EMPLOYES

       

      Thank you for your help

       

       


        • Re: Script to join 3 tables in one
          Narender Makhija

          A:

          NUM_OVED

          YEAR_COMMERCIAL

          HEURES_VALIDEES

           

          concatenation(Columns)

          QV_PAIES.QVD

          NUM_OVED

          YEAR_CONTACT

          SALAIRE_BRUT_TOTAL

           

          C:

          QV_PAIES.QVD

          NUM_OVED

          YEAR_CONTACT

          SALAIRE_BRUT_TOTAL 

           

          after this

           

           

          NEW_A:

          NUM_OVED

          if(YEAR_COMMERCIAL=YEAR_CONTACT) as  YEAR_FAMILLE

          HEURES_VALIDEES

          NUM_OVED

          YEAR_CONTACT

          SALAIRE_BRUT_TOTAL

          YEAR_FAMILLE

          resident A

           

          concatenate(NEW_A)

          C:

          NUM_OVED

          YEAR_CONTACT

          SALAIRE_BRUT_TOTAL

          • Re: Script to join 3 tables in one

            you will have to use a composite key.

             

            QV_RET_EMPLOYES :

            LOAD

            NUM_OVED,

            YEAR_COMMERCIAL as YEAR_FAMILLE,

            HEURES_VALIDEES

            FROM

            \\As01\qlikview\loader\QV_RET_EMPLOYES.QVD

            (qvd);

             

             

            Join

             

            QV_RET_OVED_VW :

            LOAD

            NUM_OVED,

            GROUPE_OVED,

            COMMERCIAL_NUM

            FROM

            \\As01\qlikview\loader\QV_RET_OVED_VW.QVD

            (qvd);

             

            Data:

            Load

            NUM_OVED,

            YEAR_FAMILLE,

            NUM_OVED & YEAR_FAMILLE as Key,

            HEURES_VALIDEES,

            GROUPE_OVED,

            COMMERCIAL_NUM

             

            resident QV_RET_EMPLOYES;


            join

             

            QV_PAIES_OVED:

            LOAD

            NUM_OVED & YEAR_CONTACT  as Key,

            SALAIRE_BRUT_TOTAL

            FROM \\As01\qlikview\loader\QV_PAIES.QVD

            (qvd);

             

            Drop Table QV_RET_EMPLOYES;

            • Re: Script to join 3 tables in one
              Marie-Sophie Blond

              Try this :

               

              QV_PAIES_FINAL :

              LOAD

                NUM_OVED,

                YEAR_COMMERCIAL as YEAR_FAMILLE,

                HEURES_VALIDEES

              FROM [\\As01\qlikview\loader\QV_RET_EMPLOYES.QVD] (qvd);

               

              LEFT JOIN (QV_PAIES_FINAL) // Join on NUM_OVED

              LOAD

                NUM_OVED,

                GROUPE_OVED,

                COMMERCIAL_NUM

              FROM [\\As01\qlikview\loader\QV_RET_OVED_VW.QVD] (qvd);

               

              LEFT JOIN (QV_PAIES_FINAL)  // Join on NUM_OVED and YEAR_FAMILLE

              LOAD

                NUM_OVED,

                YEAR_CONTACT as YEAR_FAMILLE,

                SALAIRE_BRUT_TOTAL

              FROM \\As01\qlikview\loader\QV_PAIES.QVD (qvd);

              • Re: Script to join 3 tables in one
                bobbyraj santhiogu

                Hi,

                Try this:

                 

                QV_PAIES_FINAL:

                LOAD

                NUM_OVED,

                YEAR_COMMERCIAL as YEAR_FAMILLE,

                HEURES_VALIDEES

                FROM

                \\As01\qlikview\loader\QV_RET_EMPLOYES.QVD

                (qvd);

                 

                 

                Join

                (QV_PAIES_FINAL)

                LOAD

                NUM_OVED,

                GROUPE_OVED,

                COMMERCIAL_NUM

                FROM

                \\As01\qlikview\loader\QV_RET_OVED_VW.QVD

                (qvd);

                 

                 

                Join

                (QV_PAIES_FINAL)

                LOAD

                NUM_OVED,

                YEAR_CONTACT as YEAR_FAMILLE,

                SALAIRE_BRUT_TOTAL

                FROM \\As01\qlikview\loader\QV_PAIES.QVD

                (qvd);

                 

                 

                 

                At the end you will have the expected table with the columns:

                NUM_OVED,YEAR_FAMILLE,SALAIRE_BRUT_TOTAL,GROUPE_OVED,COMMERCIAL_NUM,HEURES_VALIDEES

                • Re: Script to join 3 tables in one
                  Yusuf Ali

                  Hi ,

                         Your Code is  correct just a small change you have to do .

                  While Droping the Tables dont drop the table  "QV_RET_EMPLOYES " ;

                   

                  There is no need to use Drop Table here.

                   

                  Please let me know if there is anything .