6 Replies Latest reply: May 19, 2016 10:18 AM by Josh Good RSS

    How to JOIN several excel files in Qlik Sense

    Adrien Cottinet

      Hello All,

       

      I am pretty new in Qlik Sense and I while I thought there would be a simple solution to my problem, I can't seem to find it.

       

      I tried the Join prefix but it din't work.

       

      I am putting together pretty big excel files and I would like to merge them all into one in order to avoid synthetic keys.

       

      I have four excel files (53 columns, 1,000,000 rows) that all contain the same hearders and similar data but for different fiscal years that I would like to stack on top of each other as if they were one.

       

      Any help/guidance would be greatly appreciated,

       

      Thank you.

       

      Adrien.

        • Re: How to JOIN several excel files in Qlik Sense
          Sunny Talwar

          I think you might need a concatenate here instead of join. Look here for the difference between the two: Understanding Join, Keep and Concatenate

            • Re: How to JOIN several excel files in Qlik Sense
              Adrien Cottinet

              Hi Sunny T,

               

              Thank you for your response.

               

              I just had a look at the link you posted and from what I understand, Qlik will duplicated the records if some information is missing in a given field:


              Concatenate.JPG

              In my case, I just need to "stack" my two tables on top of each other no matter how the data is in any given row and the Outer Join seems to be more appropriate.


              Also, as my tables refer to different dates (records are in a chronological order from top to botton of the sheet), there are no primary key between them.


              What I am trying to do is to reduce load time and processor/ram needed as the synthetic tables (I have 12 of them) are pretty time consuming and ressource greedy.


              Thank you,


              Adrien.

            • Re: How to JOIN several excel files in Qlik Sense
              Josh Good

              Hi Adrien,

               

              Technically speaking there is no need to remove synthetic keys provided they make sense in your data model.  Here is a great blog post discussing this:  Synthetic Keys

               

              If you really want to get rid of them then you could use the concatenate function.  If your tables are exactly the same but for different years then you can also add a year field to each table so you know which data below to which year.  like this:

               

              Load

                   2015 as Year,

                   Column1,

                   Column2,

              ....

              ;

               

              Concatenate

              Load

                   2016 as Year,

                   Column1,

                   Column2,

              ....

              ;

               

              I hope this helps.

               

              -Josh

              Qlik

                • Re: How to JOIN several excel files in Qlik Sense
                  Adrien Cottinet

                  Hello Josh,

                   

                  Thank you for the quick response.

                   

                  The post about Synthetic Keys is very interresting and in my caase, synthetic keys are not required as I don't need my tables to interract in any other way than having the corresponding fiels "add up" (Sales in file 1 add to sales in file 2 so that I have just one measure for the sales of all the years/files I have loaded).

                   

                  I also need to filter sales by country for instance and I can't have one country dimension per files (I currently have [country] in the first file and [FY14.Country] in the second file and Qlik Sense is generating it on its own.

                   

                  I tried to use the join and concatenate prefixes without success, do you have an idea of what the code should look like in Qlik Sense?

                  (I have attached a copy of my script)

                   

                  Thank you again for your help,

                   

                  Kind Regards,

                   

                  Adrien.