Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
LP27
Contributor

Combining 4 excel file into Qlik Sense Environment

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

1 Solution

Accepted Solutions
Partner
Partner

Re: Combining 4 excel file into Qlik Sense Environment

 

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 

Work smarter, not harder
3 Replies
Partner
Partner

Re: Combining 4 excel file into Qlik Sense Environment

Hi,

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

Jordy

Climber

 

 

Work smarter, not harder
Highlighted
LP27
Contributor

Re: Combining 4 excel file into Qlik Sense Environment

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

Re: Combining 4 excel file into Qlik Sense Environment

 

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 

Work smarter, not harder