5 Replies Latest reply: Jan 15, 2015 9:46 AM by Niccolò Rossi RSS

    resident and joins

      Hi

      i have an issue when creating a qvd file from a resident table that i have created after i uploaded and joined 2 tables. The script looks something like this:

       

      TEMP:

      Load

           RepID

           DATE

           ProductID

           FamilyProductID

           Sales

           Measure2

           ...

      from qvd

       

      LEFT JOIN

      Load

           RepsID

           DATE

           RepBoss

           RepSupervisor

            ...

      from qvd

      //THEN I CREATE A TABLE THAT PUT TOGETHER INFORMATION FROM THE 2 ABOVE//

      DATAMART:

      Load

      RepsID

           DATE

           RepBoss

           RepSupervisor

      ProductID

           FamilyProductID

           Sales

      FROM TEMP

      STORE AS QVD

       

      My problem is that in my datamart i dont see the Rep Supervisor? What am i doing wrong?

       

      TX a lot

        • Re: resident and joins
          Anand Chouhan

          Hi,

           

          Confirm and check did you follow the below script as it is for creating join and then create the table with store command And what is the common field for join in this two tables i think it will be RepID if so then use same name in the tables.

           

          TEMP:

          Load

               RepID,

               DATE,

               ProductID,

               FamilyProductID,

               Sales,

               Measure2,

               ...

          From qvd

           

          LEFT JOIN

          Load

               RepsID as RepID,

               RepsID,

               DATE,

               RepBoss,

               RepSupervisor,

                ...

          from qvd

           

          DATAMART:

          Load

               RepID,

               RepsID,

               DATE,

               RepBoss,

               RepSupervisor,

               ProductID,

               FamilyProductID,

               Sales

          Resident TEMP;

          STORE DATAMART AS DATAMART.QVD;


          Drop table TEMP;


          Regards

          Anand

          • Re: resident and joins
            Joseph Simmons

            Hi Niccolo,

             

            you have RepID in the first table and RepsID in the second, if you want to join on the ID the fields will need to be the same name.

            Based on that the only field you are currently joining on is DATE, which if it was working correctly would be giving you have big duplication in your temp table, so check the formats of DATE in the two tables.

             

            Also, the only extra element you are doing in your final table looks to be loading a field subset (dropping Measure2),

            you can just use 'drop field' and then just store the temp table, no real need to do the resident

             

            hope that helps

            Joe

            • Re: resident and joins

              RepID and RepsID are not a problem (it is my typo). And this is a simplification of my script, i have many measures.

              The script is actually this:

               

              TEMP:

              LOAD COD_FB,

              date(DATA_COMPETENZA,'MM/YYYY') as DATA_COMPETENZA,

              //COD_PRODOTTO,

              //TIPO_PRODOTTO,

              //TIPO_COMPARTO,

              DES_Prodotto_TipoRisparmio1,

              DES_Prodotto_BusinessLine2,

              DES_Prodotto_Famiglia3,

              DES_Prodotto,

              NAV,

              FFEE_PERS,

              FFEE_OVER,

              MFEE_PERS,

              MFEE_OVER,

                   IMP_RACCOLTA_NETTA

              FROM

              ETL1_Dati_Finanziari.qvd(qvd)

              Where year(DATA_COMPETENZA)>='2013';

               

              //LEFT JOIN

              LOAD

              //ANNO,

              //MESE,

              date(DATA_COMPETENZA,'MM/YYYY') as DATA_COMPETENZA,

              COD_Regional ,

              DES_Regional ,

              COD_2Lvl ,

              DES_2Lvl ,

              COD_3Lvl,

              DES_3Lvl ,

              NOME_FB ,

              COD_FB,

              DATA_INGRESSO_FB,

              //DATA_NASCITA_FB,

              DATA_USCITA_FB,

              LIVELLO_CARRIERA,

              PERC_PROVVIGIONALE,

              RUOLO_COMMERCIALE,

              TIPO_PROVENIENZA,

              COD_AGENZIA,

              QUALIFICA,

              TIPOLOGIA,

                   [DT APERTURA],

              DES_Ripartizione,

              DES_Provincia,

              DES_Regione,

              DES_Comune

              FROM

              ETL1_All_info_FB.qvd (qvd)

              Where year(DATA_COMPETENZA)>='2013';

               

              //Datamart_Prodotti:

              //LOAD

              // month(DATA_COMPETENZA) as PDT_DTA_Mese,

              // year(DATA_COMPETENZA) as PDT_DTA_Anno,

              // DATA_COMPETENZA as DTA_MAKEDATE,

              //     DES_Prodotto_TipoRisparmio1,

              // DES_Prodotto_BusinessLine2,

              // DES_Prodotto_Famiglia3,

              // DES_Prodotto,

              // COD_Regional ,

              // DES_Regional ,

              // COD_2Lvl ,

              // DES_2Lvl ,

              // COD_3Lvl,

              // DES_3Lvl ,

              //     NOME_FB ,

              //     COD_FB,

              //// COD_Regional as PDT_COD_Regional,

              //// DES_Regional as PDT_DES_Regional,

              //// COD_2Lvl as PDT_COD_2Lvl,

              //// DES_2Lvl as PDT_DES_2Lvl,

              //// COD_3Lvl as PDT_COD_3Lvl,

              //// DES_3Lvl as PDT_DES_3Lvl,

              //// NOME_FB as PDT_DES_FB,

              //// COD_FB as PDT_COD_FB,

              // if(Year(DATA_INGRESSO_FB)= 2014,1,0) as PDT_FLG_Ingresso,

              // if(Year(DATA_USCITA_FB)=2014,1,0) as PDT_FLG_Uscito,

              // NAV as PDT_DBL_AUM,

               

              // IMP_RACCOLTA_NETTA as PDT_DBL_RN,

              // MFEE_PERS as PDT_DBL_MGdirette,

               

              // MFEE_OVER as PDT_DBL_MGover,

              // FFEE_PERS as PDT_DBL_FFdirette,

               

              // FFEE_OVER as PDT_DBL_FFover,

              // MFEE_PERS+MFEE_OVER+FFEE_PERS+FFEE_OVER as PDT_DBL_Remunerazione,

              // FFEE_PERS+FFEE_OVER as PDT_DBL_Front,

              // MFEE_PERS+MFEE_OVER as PDT_DBL_Management,

              // MFEE_OVER+FFEE_OVER as PDT_DBL_Over,

              // MFEE_PERS+FFEE_PERS as PDT_DBL_Dirette

              //   

              //Resident TEMP;

              //STORE Datamart_Prodotti into ETL2_Datamart_Prodotti.qvd(qvd);

              //DROP Tables TEMP,Datamart_Prodotti;