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.
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:
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?
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).