3 Replies Latest reply: Feb 17, 2017 4:22 AM by Andrey Khoronenko RSS

    Qlik Sense - Mapping two Tables together

    Tom Baker

      I have two tables that both contain "Company Name" and "Project", and then multiple non-matching columns.

       

      I need the tables to act how a synthetic key would where the non-matching columns can communicate due to the Company Name and Project being the same.

       

      All other tables in my data load have had no problem connecting through synthetic keys but there is this one instance where two tables are not communicating well so I want to map them together.

       

      How do I do this in the data load editor?

       

      Tom

        • Re: Qlik Sense - Mapping two Tables together
          Shraddha Gajare

          You can connect those tables by creating Composite key of Company Name and Project

          • Re: Qlik Sense - Mapping two Tables together
            Zhihong He

            Hi, Tom,

             

            Generally, synthetic key MUST be avoided.

            If you only have few tables, Qlik maybe can still process it correctly, but if you have more tables, synthetic key not only make wrong mapping and does not work, it can also cause disaster by eating all your memory and even disk and crash your system, we had this bloody lesson before when adding several extra tabled to a exist app which have synthetic keys.

             

            To avoid synthetic key, you need delete or join the tables with same key, and also using composite key to connect the tables as Shraddha pointed out, which can fix your current problem. But if you add more tables to your current app with synthetic key, you may get trouble gain in the future.

             

            Link below is a blog talk about how to avoid synthetic key.

            https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

             

            Zhihong 

            • Re: Qlik Sense - Mapping two Tables together
              Andrey Khoronenko

              Hi Tom.

               

              The idea of creating a synthetic key can be understood from this fragment of code with comments.

               

              //create source tables Table01 and Table02

              Table01:

              LOAD

              'Company'&Div(Rand()*10, 1) as Company,

              'Poject'&Div(Rand()*10, 1)  as Project,

              'Dimension'&Div(Rand()*10, 1)  as Dimension1,

              Div(Rand()*10, 1) as Volume1

              AutoGenerate 1000;

               

               

              NoConcatenate

              Table02:

              LOAD

              'Company'&Div(Rand()*10, 1) as Company,

              'Poject'&Div(Rand()*10, 1)  as Project,

              'Dimension'&Div(Rand()*10, 1)  as Dimension2,

              Div(Rand()*10, 1) as Volume2

              AutoGenerate 1000;

               

               

              //form a synthetic key

              NoConcatenate

              Table1:

              LOAD*,

              Company & '|' & Project as %Key

              Resident Table01;

               

               

              NoConcatenate

              Table2:

              LOAD*,

              Company & '|' & Project as %Key

              Resident Table02;

               

               

              DROP Tables Table01, Table02;

               

              //create a connection table

              ConnectTable:

              Load Distinct

              %Key,

              %Key as %TimeKey,

              Company,

              Project

              Resident Table1;

               

               

              Concatenate (ConnectTable)

              Load Distinct

              %Key,

              Company,

              Project

              Resident Table2

              Where Not Exists(%TimeKey, %Key);

               

               

              //delete unneeded fields

              Drop Fields %TimeKey, Company, Project

              From ConnectTable;

               

               

              Drop Fields

              Company, Project

              From Table1;

               

               

              Drop Fields

              Company, Project

              From Table2;

               

              Result

               

              1.jpg

               

              Regards,

              Andrey