20 Replies Latest reply: Feb 15, 2017 1:38 AM by surendra j RSS

    Create a Join with specific fields

    Kevin Somosera

      Hi,

       

      Im new at Qlik Sense and Im wondering if you could help me with my problem. Does anyone here know how to join two tables using 'Data Load Editor' on Qlik Sense? Does Qlik Sense Join can behave like SQL? BTW, Im using Excel File with 900K rows FYI. For further information to my question, here is my sample:

       

      ***Here I want to use this table, named Table1

       

      [Table1]:

      LOAD

      [Name]

      [Nickname]

      [Address]

      [Number]

      [Birthday]

      FROM [lib://DOCUMENTS/data.xlsx]

      (ooxml, embedded labels, table is data);

       

      ***And join it to Table2, Containing

       

      [Table2]:

      LOAD

      [Name] AS Name[

      [Address]

      [Bank No]

      [Bank Type]

      FROM [lib://DOCUMENTS/security.xlsx]

      (ooxml, embedded labels, table is security);

       

      **Using Name and Address only...

       

      NOTE: I don't want to use Qlik Sense auto-join because it returns with "Synthetic Key"

       

      THANK YOU!

        • Re: Create a Join with specific fields
          surendra j

          Try with link table....to aviod synthatic key.

           

          [Table1]:

          LOAD

          //[Name]

          [Nickname],

          //[Address]

          [Name]'&'[Address] as key,

          [Number],

          [Birthday]

          FROM [lib://DOCUMENTS/data.xlsx]

          (ooxml, embedded labels, table is data);

           

          [Table2]:

          LOAD

          //[Name]

          //[Address]

          [Name]'&'[Address] as key,

          [Bank No],

          [Bank Type]

          FROM [lib://DOCUMENTS/security.xlsx]

          (ooxml, embedded labels, table is security)

           

          link_table:

          [Name],

          //[Nickname]

          [Address],

          [Name]'&'[Address] as key

          //[Number]

          //[Birthday]

          FROM [lib://DOCUMENTS/data.xlsx]

          (ooxml, embedded labels, table is data);

          concatinate(link_table)

          LOAD

          [Name],

          [Address],

          [Name]'&'[Address] as key

          //[Bank No],

          //[Bank Type]

          FROM [lib://DOCUMENTS/security.xlsx]

          (ooxml, embedded labels, table is security)

           

          By Link table...we have Name,Address and key.

          If you want to extract some other fields,those are available in [Table1] and [Table2]

          Here we have data model with 3 tables..[Table1],[Table2] and Link table with no synthetic keys.

          • Re: Create a Join with specific fields
            Kaushik Solanki

            Hi,

             

            You can try this script.

             

            [Table1]:

            LOAD

            [Name]&[Address]as Key,

            [Name]

            [Nickname]

            [Address]

            [Number]

            [Birthday]

            FROM [lib://DOCUMENTS/data.xlsx]

            (ooxml, embedded labels, table is data);

             

            ***And join it to Table2, Containing

             

            [Table2]:

            LOAD

            [Name]&[Address]as Key,

            [Bank No]

            [Bank Type]

            FROM [lib://DOCUMENTS/security.xlsx]

            (ooxml, embedded labels, table is security);

             

             

            You can search for how to remove synthetic Key and you will find many more ways of doing it.

             

             

            Regards,

            Kaushik Solanki

            • Re: Create a Join with specific fields
              Rahul Pawar

              Hello Kevin,

               

              Trust that you are doing good!

               

              Below are two approaches you can use to solve your problem:

               

              1. Using JOIN (In below given example I have used INNER JOIN; you can make use LEFT/RIGHT/JOIN as per your need)

               

              [Table1]:
              LOAD
              [Name],
              [Nickname],
              [Address],
              [Number],
              [Birthday]
              FROM [lib://DOCUMENTS/data.xlsx]
              (ooxml, embedded labels, table is data);
              INNER JOIN (Table1)
              [Table2]:
              LOAD
              [Name] AS Name,
              [Address],
              [Bank No],
              [Bank Type]
              FROM [lib://DOCUMENTS/security.xlsx]
              (ooxml, embedded labels, table is security);
              
              
              

               

              2. Creating Composite/Complex Key (In below given example I have created composite key using Name and Address; This will eliminate the Synthetic key issue)

               

              [Table1]:
              LOAD
              [Name] & '|' & [Address] Key,
              [Nickname],
              [Number],
              [Birthday]
              FROM [lib://DOCUMENTS/data.xlsx]
              (ooxml, embedded labels, table is data);
              
              [Table2]:
              LOAD
              [Name] & '|' & [Address] Key,
              [Name] AS Name,
              [Address],
              [Bank No],
              [Bank Type]
              FROM [lib://DOCUMENTS/security.xlsx]
              (ooxml, embedded labels, table is security);
              
              

               

              Hope this will be of help.

               

              Regards!

              Rahul