4 Replies Latest reply: Sep 19, 2017 9:57 AM by Lucio Gutierrez RSS

    confusion on Link table creation

    askqlikaq N

      I have 6 tables. All the tables are linked on the database end with one columns or more. Can you please, suggest on how we can join all these 6 tables.

       

      I have to avoid creating synthetic keys as that will not help my model.

       

      Any suggestions are highly appreciated.. Thanks

       

      t1 - col1, col2, col3,colr,cole

      t2 - col1, colx, coly

      t3 - col2, cola, colb

      t4 - col3, cold

      t5 - col1, col2,colh

      t6 - col2,col3,colf

       

       

      col1,col2,col3 are not distinct in all the tables. They are bound to repeat in all the tables. Following is the model.

       

      Capture.JPG

        • Re: confusion on Link table creation
          Lucio Gutierrez

          Hi Ask...

           

          You can add a new index colum in each table of  course that you must manage the relation type between they, and in the transformation app you can use a mapping funtion to relation all tables.

           

          ex.

          Segmento_Map:

          Mapping

          LOAD

              Segmento,

              Id_Segmento             (new column)

          Resident Segmento;

           

          Ventas_Comercial:

          Load

            ApplyMap('Segmento_Map', [0DIVISION]) as Id_Segmento,

            Resident Comercial_Ventas_Tmp;

          etc...

          • Re: confusion on Link table creation
            Lucio Gutierrez

            Ask,

             

            In Qlik you can manage the ETL proccess with an app for each part of the proccess... this way to work help you to have control over the total proccess, is just a way to work. the idea is the next:

            1- Exraction app.- Extract the data from the source database and store the result in an qvd file.

            2- Trasformation appa - Make all the trasformation over the data to make business sense information, and also store de results in a qvd.

            3- And finaly you have all the neccesary to make your dashboard.

            As i told you, you can do all these in a sigle app, but you get more control if do in this way.

             

            I hope this help you

             

            regards.