topic Combining 4 excel file into Qlik Sense Environment in App Development

Combining 4 excel file into Qlik Sense Environment

LP27 — Sat, 16 Nov 2024 05:39:01 GMT

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

Re: Combining 4 excel file into Qlik Sense Environment

JordyWegman — Thu, 06 Jun 2019 11:40:00 GMT

Hi,

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

Jordy

Climber

Re: Combining 4 excel file into Qlik Sense Environment

LP27 — Thu, 06 Jun 2019 12:25:31 GMT

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

Re: Combining 4 excel file into Qlik Sense Environment

JordyWegman — Thu, 06 Jun 2019 13:26:39 GMT

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