Combining 4 excel file into Qlik Sense Environment

[LP27]

Hi Qlikers, 

I have 4 Excel files(xlsx) with 5 column header common in all files remaining columns are different. 

All the excel file carry same name and only last letter changes. Ex: File2012,File2013,File2014,File2015.

Now i want to load all the excel files into Qlik sense, Data load editor together.

Example view of fields in files:-

File1:

[Project],
[Project Name],
[Decision Point],
[Employee],
[Employee Name],
[OCT 2008],
[NOV 2008],
[DEC 2008],..........til [Dec 2014]

File2:

[Project],
[Project Name],
[Decision Point],
[Employee],
[Employee Name],
[OCT 2015],
[NOV 2015],
[DEC 2015]..... til....[2016].

File3:

[Project],
[Project Name],
[Decision Point],
[Employee],
[Employee Name],
[OCT 2017],
[NOV 2017],
[DEC 2017]..... til....[Dec 2018] .

File4:

[Project],
[Project Name],
[Decision Point],
[Employee],
[Employee Name],
[OCT 2019],
[NOV 2019],
[DEC 2019]..... til....[Dec 2020] .

These are the four files with common column in Bold.

Could some one guide or show me how to load all these files into Data load editor in Qlik Sense?

I have attached 3 sample Excel file and 4th excel file is similar to all of these. [ Qlik community only lets me to uplaod 3 excel file so couldn't upload 4th one.]  

Note : values inside columns are dummy and headers are same in all 4 excel files.

Thanks in advance,

LP27

[JordyWegman]

Hi,

Can you send an example excel file? Then we can make this.

Jordy

Climber

[LP27]

I have uploaded sample excel files and 4th file is similar to all the 3 files.

[JordyWegman · Accepted solution]

Hi,

This is the script for the first two. The third is also easy, just copy the 2nd part and edit the connection for the third. By using a crosstable, you can better use your data. I've called the months/year -> Date and the other Values.

The tables you are adding will auto concatenate because they have the same column names.

[YourData]:
CROSSTABLE (Date,Values,5)
LOAD
	[Project No],
	[Project Name],
	[Decision Point],
	[Employee No],
	[Employee Name],
	[OCT 2008],
	[NOV 2008],
	[DEC 2008],
	[JAN 2009],
	[FEB 2009],
	[MAR 2009],
	[APR 2009],
	[MAY 2009],
	[JUN 2009],
	[JUL 2009],
	[AUG 2009],
	[SEP 2009]
 FROM [lib://Downloads/File1.xlsx]
(ooxml, embedded labels, table is Sheet1);


CROSSTABLE (Date,Values,5)
LOAD
	[Project No],
	[Project Name],
	[Decision Point],
	[Employee No],
	[Employee Name],
    "OCT 2010",
    "NOV 2010",
    "DEC 2010",
    "JAN 2010",
    "FEB 2010",
    "MAR 2010",
    "APR 2010",
    "MAY 2010"
FROM [lib://Downloads/File2.xlsx]
(ooxml, embedded labels, table is Sheet1);

Jordy

Climber