How to JOIN several excel files in Qlik Sense

[#1]

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.

[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

[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

[#4]

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:

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.

[#5]

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.

[undergrinder]

Hi Adrien,

Try this:

Table_name:

Load

     Key

     ,A

From  [table];

Outer join(Table_name)

Load

     Key,

     ,C

From [other table];

Qlik Sense will recognise the key is the Key column.

G.

[Josh_Good]

Hi Adrien,

After seeing your script, I am quite confident that the approach I described above is the best one.  Don't forget to add a field for year so you know where the data come from (in my example above the Year field is doing that). 

-Josh

Qlik