10 Replies Latest reply: Apr 8, 2018 3:10 PM by Brunello Menicucci RSS

    Issues with Link Table

    Brunello Menicucci

      Hello folks,

      I need your help with a strange thing it happens to me. Consider the following script:

       

      >>>>>>>>>>>>>>>>>>>

      AreaBusinessMap:

      MAPPING LOAD

         TipoBusiness

         ,%IDTipoBusiness

      FROM $(vG.LoadPath)\AreeBusiness.qvd (qvd);

       

      FatturatoInterventi:

      LOAD DISTINCT

          %IDFattInterventi

          , IDFattInterventi

          , IDKeyVendite

          , ApplyMap('AreaBusinessMap', TipoBusiness, '<Null>') AS %IDTipoBusiness

          , ApplyMap('AreaBusinessMap', TipoBusiness, '<Null>') AS %CheckTipoBusiness

          , DataIntervento

          , TipoDocInt

          , Teci_nteKey

          , IDTecnico

          , [Serial Intervento]

          , [Provenienza Intervento]

          , NUMTECINT

          , FlagProvenienza

          , ValoreMovInt

      FROM $(vG.ExtractPath)\TempFatturatoInterventi.qvd (qvd);

       

      AreeBusiness:

      LOAD

         TipoBusiness

         ,%IDTipoBusiness

      FROM $(vG.LoadPath)\AreeBusiness.qvd (qvd);

       

      >>>>> NOW LOADING TABLES IN A LINK TABLE <<<<<<<<<<<<<

      LinkTable:

      //Carico i tipi di Business nella Link Table

      LOAD Distinct

      %IDTipoBusiness

      Resident AreeBusiness;

       

      //Carico FatturatoInterventi nella Link Table

      CONCATENATE

      LOAD Distinct

      %IDFattInterventi

      ,%IDTipoBusiness

      ,'Fatturato Interventi' AS %DateType

      Resident FatturatoInterventi;

      DROP Fields

      %IDTipoBusiness

      ,IDTecnico

      From FatturatoInterventi;

       

      I have this two tables: the linking field is TipoBusiness. The strange thing is that if I load the two table without putting them in a "link table" I get the following:

       

      Screenshot 2018-04-06 18.59.23.png

      otherwise, when loading in a link table, it seems that the linking field is not recognized and I get a full join!

      Screenshot 2018-04-06 19.00.03.png

      am I missing something?

      Any help is much appreciate!

      Thanks a lot.

      Brunello

        • Re: Issues with Link Table
          Digvijay Singh

          Not sure but do you need CONCATENATE here when using link table? May be Left JOIN is needed.

          • Re: Issues with Link Table
            Mauri Kawamura

            Hi,

             

            When you concatenate, you will have a full join.

             

            if you want to join the tables, using the link field, you need to use left join or right join.

             

            Regards,

            • Re: Issues with Link Table
              Sasidhar Parupudi

              AreaBusinessMap:

              MAPPING LOAD

                 TipoBusiness

                 ,%IDTipoBusiness

              FROM $(vG.LoadPath)\AreeBusiness.qvd (qvd);

               

              FatturatoInterventi:

              LOAD DISTINCT

                  %IDFattInterventi

                  , IDFattInterventi

                  , IDKeyVendite

                  , ApplyMap('AreaBusinessMap', TipoBusiness, '<Null>') AS %IDTipoBusiness

                  ,%IDFattInterventi &'|'& ApplyMap('AreaBusinessMap', TipoBusiness, '<Null>')  AS %Key

                  , ApplyMap('AreaBusinessMap', TipoBusiness, '<Null>') AS %CheckTipoBusiness

                  , DataIntervento

                  , TipoDocInt

                  , Teci_nteKey

                  , IDTecnico

                  , [Serial Intervento]

                  , [Provenienza Intervento]

                  , NUMTECINT

                  , FlagProvenienza

                  , ValoreMovInt

              FROM $(vG.ExtractPath)\TempFatturatoInterventi.qvd (qvd);

               

              AreeBusiness:

              LOAD

                 TipoBusiness

                 ,%IDTipoBusiness

              FROM $(vG.LoadPath)\AreeBusiness.qvd (qvd);

               

              >>>>> NOW LOADING TABLES IN A LINK TABLE <<<<<<<<<<<<<

              LinkTable:

              //Carico i tipi di Business nella Link Table

              LOAD Distinct

                   %IDTipoBusiness

              Resident AreeBusiness;

               

              //Carico FatturatoInterventi nella Link Table

              CONCATENATE

              LOAD Distinct

                   %Key

              Resident FatturatoInterventi;

              DROP Fields

              %IDTipoBusiness

              ,IDTecnico

              From FatturatoInterventi;

              • Re: Issues with Link Table
                Mikael Scorielle

                Hello Bruno

                 

                I don't see why you're creating a link table in your case.

                 

                Link tables make sense when you have multiple large (fact) tables that share a series of common dimensions, but for some (good or bad) reason, you want to keep those tables separated.

                (there are a lot of good references like Concatenate vs Link Table)

                In your case you are "just" creating an association between a fact table "FatturatoInterventi" and the AreeBusiness (dimension table).

                And that is even unnecessary as you used the mapping table at first to bring the TipoBusiness field into your fact table with the apply map.


                you ended  your script by removing the key field between your 2 tables (%IDTipoBusiness)

                this explains why when creating a table like you did, you duplicate all of your lines by the number of TipoBusiness values that you have (3) (8226 8227 8232), which is obviously incorrect (as you already knew by yourself with the %TypoBusinessCheck field)

                  • Re: Issues with Link Table
                    Brunello Menicucci

                    Hello Mikael,

                    thanks for your answer.

                    THe reason why I’m making a link table is that I have several tables and I’d like to have a connection between this two - i.e., I want to select a record in AreeBusiness and have it reflected to FatturatoInterventi.

                    I’m quite a newbie but in my experience when I want to make such a connection, I need to load the key of a certain table and then delete it from the table, to avoid syntethic keys.

                    Here after you can see the whole LinkTable; I asked the community about these two particular tables as a result of a troubleshooting.

                    You mean I’d do it in a different way?

                    thanks again

                    Brunello

                     

                    [Link Table]:

                    LOAD Distinct

                    %IDCalendar

                    ,DataID AS %CanonicalDateID

                    Resident Calendar;

                    DROP Field

                    LastUpDate

                    ,Period1

                    FROM Calendar;

                     

                    //Carico le date Rolling nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %AsOfPeriodID

                    Resident AsOfPeriodTable;

                    DROP Field

                    DataID

                    FROM AsOfPeriodTable;

                     

                    //Carico i dati del Pianoconti nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDConto

                    Resident Pianoconti;

                    DROP Fields

                    LastUpDate

                    From Pianoconti;

                     

                    //Carico i tipi di Business nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDTipoBusiness

                    Resident AreeBusiness;

                    DROP FIELD

                    U_SETT

                    FROM AreeBusiness;

                     

                    //Carico gli Agenti nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDAgente

                    ,%IDTecnici

                    Resident Agenti;

                    DROP Fields

                    %IDTecnici,

                    LastUpDate

                    From Agenti;

                     

                    //Carico i Tecnici nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDTecnici

                    Resident Tecnici;

                    //DROP Fields

                    // %IDTecnici,

                    // LastUpDate

                    // From Agenti;

                     

                    //Carico i Clienti nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDCustomer

                    ,%IDAgente

                    Resident Clienti;

                    DROP Fields

                    %IDAgente,

                    IDAgente,

                    LastUpDate

                    From Clienti;

                     

                    // //Carico i Clienti nella Link Table

                    // CONCATENATE

                    // LOAD Distinct

                    // %IDDesDive

                    // ,%IDCustomer

                    // Resident DestDiversi;

                    // DROP Fields

                    // %IDCustomer,

                    // LastUpDate

                    // From DestDiversi;

                     

                    //Carico le Rappresentate nella LInk Table

                    CONCATENATE

                    LOAD Distinct

                    %IDRappresentata

                    Resident Rappresentate;

                    DROP FIeld

                    %IDClasse_MBO

                    ,CodCat

                    FROM Rappresentate;

                     

                    //Carico i Prodotti nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDProduct

                    ,%IDRappresentata

                    Resident Prodotti;

                    DROP Fields

                    %IDRappresentata

                    ,LastUpDate

                    From Prodotti;

                     

                    //Carico i dati degli Ordini nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDOrdini

                    ,%IDAgente

                    ,%IDProduct

                    ,%IDCustomer

                    ,%IDTipoBusiness

                    ,DataID AS %OrdiniDate

                    ,%IDCalendar

                    ,'Ordini' AS %DateType

                    Resident Ordini;

                    DROP Fields

                    %IDCalendar

                    ,%IDAgente

                    ,%IDProduct

                    ,%IDCustomer

                    ,%IDClasse_MBO

                    ,%IDTipoBusiness

                    ,DataID

                    ,IDAgente

                    ,Agente

                    ,IDProduct

                    ,CodArt

                    ,%IDGamma

                    ,U_SETT

                    ,TipoBusiness

                    //,CodTecnico

                    ,LastUpDate

                    From Ordini;

                     

                    // //Carico gli Interventi nella Link Table

                    // CONCATENATE

                    // LOAD Distinct

                    // %IDInterventi

                    // ,%IDCalendar

                    // ,%IDTecnici

                    // ,DataID AS %InterventiDate

                    // ,'Interventi' AS DateType

                    // Resident Interventi;

                    // DROP Fields

                    // %IDCalendar,

                    // %IDTecnici,

                    // DataID,

                    // LastUpDate

                    // From Interventi;

                     

                    //Carico i fatti delle Vendite nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDSalesKey

                    ,%IDCalendar

                    ,%IDCustomer

                    ,%IDProduct

                    ,%IDAgente

                    ,%IDBudget

                    ,%IDRappresentata

                    ,%IDTipoBusiness

                    ,%IDFattInterventi

                    //,%IDTecnici

                    ,DataID AS %VenditeDate

                    ,'Vendite' AS %DateType

                    Resident Vendite;

                    DROP Fields

                    %IDCalendar

                    ,%IDCustomer

                    ,%IDProduct

                    ,%IDAgente

                    ,Anno

                    ,%IDBudget

                    ,%IDGamma

                    ,%IDFattInterventi

                    //,%IDTecnici

                    ,IDAgente

                    ,Agente

                    ,IDProduct

                    ,IDKeyVendite

                    //,Teci_nteKey

                    //,%IDClasse_MBO

                    ,%IDRappresentata

                    ,%IDTipoBusiness

                    ,CodArt

                    //,CatMerc

                    ,DataID

                    ,U_SETT

                    ,LastUpDate

                    From Vendite;

                     

                    //Carico il FatturatoInterventi nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDFattInterventi

                    ,%IDCalendar

                    ,%IDAgente

                    ,DataID AS %FattInterventiDate

                    ,'Fatturato Interventi' AS DateType

                    Resident FatturatoInterventi;

                    DROP Fields

                    %IDCalendar,

                    %IDAgente,

                    //%IDSalesKey,

                    //IDKeyVendite,

                    //DPCODICE,

                    //DPCODICE2,

                    DataID

                    From FatturatoInterventi;

                     

                    ///Carico i dati delle scadenze nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDARDettKey

                    ,%IDCalendar

                    ,%IDAgente

                    ,%IDCustomer

                    ,DataID AS %ScadenzeDate

                    ,'Scadenze' AS %DateType

                    Resident AR_Dettaglio;

                    DROP Fields

                    %IDAgente

                    ,%IDCalendar

                    ,%IDCustomer

                    ,%IDGamma

                    ,%IDAgente

                    ,IDAgente

                    ,DataID

                    ,Tipo

                    ,NumDoc

                    From AR_Dettaglio;

                     

                    ///Carico i Costi Mensili nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDCostiMensili

                    ,%IDCalendar

                    ,%IDAgente

                    ,DataID AS %CostiMensiliDate

                    ,'CostiMensili' AS %DateType

                    Resident CostiMensili;

                    DROP Fields

                    %IDCalendar

                    ,%IDAgente

                    ,IDAgente

                    ,DataID

                    ,ID

                    FROM CostiMensili;

                     

                    //Carico i Bilancini nella Link Table

                    // /*

                    CONCATENATE

                    LOAD Distinct

                    %IDAccountBalance

                    ,%IDCalendar

                    ,%IDConto

                    ,DataID AS %BilanciniDate

                    ,'AccountBalance' AS %DateType

                    Resident AccountBalance;

                    DROP Fields

                    %IDCalendar

                    ,%IDConto

                    ,DataID

                    ,MonthYear

                    FROM AccountBalance;

                     

                    //Carico il Forecast nella Link Table

                    CONCATENATE

                    LOAD Distinct

                    %IDForecast

                    ,DataID AS %ForecastDate

                    ,'Forecast' AS %DateType

                    Resident Forecast;

                    DROP Fields

                    IDRicla

                    ,DataID

                    FROM Forecast;

                     

                    /*

                    CONCATENATE

                    LOAD DISTINCT

                    %IDCalendar

                    Resident Cashflow;

                    DROP FIELDS

                    %IDCalendar

                    ,DataID

                    FROM Cashflow;