3 Replies Latest reply: Jun 25, 2013 9:11 AM by Jason Newman RSS

    Link Table Theory

      I saw that Link tables are include in the Qlikview Best Practices, so I have read various explanations and I thought I would try to create a basic scenario.

      I have created the following tables:

      Table1:

      LOAD * INLINE [

      %Key,A,B,%Colour,%Size

      1,A1,B1,1,2

      2,A2,B2,2,2

      3,A3,B3,1,2

      ];

       

       

      Table2:

      LOAD * INLINE [

      %Key,C,D,%Colour,%Size

      1,C1,D1,1,1

      2,C2,D2,2,1

      ];

       

      ColourDesc:

      LOAD * INLINE [

      %Colour, CDescription

      1,Red

      2,Blue

      ];

       

      SizeDesc:

      LOAD * INLINE [

      %Size, SDescription

      1,Small

      2,Large

      ];

       

      Then looking at a 'template' to create the link table:

      LNK_TABLE:

      load distinct

      %KEY_FACT1_ID        AS %KEY_LNK_ID

      ,    %KEY_DIM1_ID        AS %%KEY_DIM1_ID

      ,    %KEY_DIM2_ID        AS %%KEY_DIM2_ID

      RESIDENT Fct1;

      CONCATENATE

      load distinct

      %KEY_FACT2_ID        AS %KEY_LNK_ID

      ,    %KEY_DIM1_ID        AS %%KEY_DIM_ID

      ,    %KEY_DIM2_ID        AS %%KEY_DIM2_ID

      RESIDENT Fct2;

       

      I created the following (amoungst numerous other attempts!):

      Link:

      Load distinct

         %Key,

         %Colour,

         %Size

      Resident Table1;

       

      Concatenate

       

      Load distinct

         %Key,

         %Colour,

         %Size

      Resident Table2;

       

      The result is horrible. Can someone explain where I am going wrong, or is it just a bad example?

       

      Thanks

       

      Jason

       

       

        • Re: Link Table Theory
          Mark Sheraton

          Try this

           

          Table1:

          load *

                    , %Key & %Colour & %Size as %comb_key

          ;

          LOAD * INLINE [

          %Key,A,B,%Colour,%Size

          1,A1,B1,1,2

          2,A2,B2,2,2

          3,A3,B3,1,2

          ];

           

           

          Table2:

          load *

                    , %Key & %Colour & %Size as %comb_key

          ;

          LOAD * INLINE [

          %Key,C,D,%Colour,%Size

          1,C1,D1,1,1

          2,C2,D2,2,1

          ];

           

           

           

           

           

          ColourDesc:

          LOAD * INLINE [

          %Colour, CDescription

          1,Red

          2,Blue

          ];

           

          SizeDesc:

          LOAD * INLINE [

          %Size, SDescription

          1,Small

          2,Large

          ];

           

           

           

           

          link:

          load %comb_key

          ,%Key

          ,%Colour

          ,%Size

          Resident Table1;

                    Concatenate(link)

                              load %comb_key

                              ,%Key

                              ,%Colour

                              ,%Size

                              Resident Table2;

           

          DROP FIELDS %Key , %Colour ,%Size FROM  Table1;

          DROP FIELDS %Key , %Colour ,%Size FROM  Table2;

          • Re: Link Table Theory
            anant dubey

            Hi,

             

            inorder to create a linked data model follow the below steps:

             

             

            Table1:

            autonumber(x,'table1') as key1,

            y,

            z,

            t,

            y

            from tab1.qvd;

             

            Table2:

             

            autonumber(g,'table2') as  key2,

            y,

            z

            m,

            n

            from tab2.qvd

             

            Table3:

             

            autonumber(r,'table3') as   key3,

            y,

            z,

            f

            from tab3.qvd;

             

            Final:

            load

            key1,

            y,

            z

            Resident Table1;

            drop fields y,z from Table1;

             

            concatenate

             

            load

            key2,

            y,

            z

             

            Resident Table2;

            drop fields y,z from Table2;

             

            concatenate

            load

            key3,

            y,

            z,

            Resident Table3;

            drop fields y,z from Table3;

             

             

            Note:

            you can see all the tables 1,2,3 combined into one table and its field key1, key2,key3

            linked to respective table1 , table2, table3 and each individual tables has distinct dimension fields

            and therfore no circular loop will form,

             

            Rgards,

            Anant