Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

knkirubakaran
New Contributor III

Load multiple excel files in Qlikview

can please help me on this....

I have excel files coming monthly and need to be loaded that excel every month and create table.

Instruction:

Show asset Download count of each month

Filter1 - Blank column as named to 'cross'

Excel screenshot:

Capture1.PNG

Load the attached excel file and need to create table like below:

table.PNG

Thanks

1 Solution

Accepted Solutions
Not applicable

Re: Load multiple excel files in Qlikview

Hi,

Sorry for the delay. Is this what you need:

If so, please see the attached qvw and I hope this helps!

Stan

10 Replies
Highlighted
harishkumarg
Contributor III

Re: Load multiple excel files in Qlikview

Script:

Load

'Jun2016' as Month-Year,

ID,

[Downloads Jun 2016] as Download,

Filter1

From

Download-click-Jun2016.xlsx

;

Concatenate

Load

'Jul2016' as Month-Year,

ID,

[Downloads Jun 2016] as Download,

Filter1

From

Download-click-Jul2016.xlsx

;

Concatenate

Load

'Aug2016' as Month-Year,

ID,

[Downloads Aug 2016] as Download,

Filter1

From

Download-click-Aug2016.xlsx

;

UI:

Create a straight table.

Dimension = Filter1

Expression 1 [Downloads Jun 2016] = Sum({<Month-Year={'Jun2016'}>}Download)

Expression 2 [Downloads Jul 2016] = Sum({<Month-Year={'Jul2016'}>}Download)


Expression 3 [Downloads Aug 2016] = Sum({<Month-Year={'Aug2016'}>}Download)


Disadvantage of the above method:


You will have to add each month's data manually each time you receive it. (Both in script and Expression)


Alternate approach:

Have a single excel where you will enter all the months data. Modified template is attached.

Change the dimension and expression of your report,

Dimensions - Filter1 and Month-Year

Expression - Sum(Download)



Hope this helps!


Regards

Harish

Not applicable

Re: Load multiple excel files in Qlikview

I'm not understanding what your goal is. Can you please restate?

knkirubakaran
New Contributor III

Re: Load multiple excel files in Qlikview

I need to create Cross table based on the attached excel sheets. It comes monthly with incremental load.

karthikoffi27se
Contributor III

Re: Load multiple excel files in Qlikview

Hi

You can load all excel files in one go if at all you have all the excel files named the same.

Asset:

Load *

From Download -clicks * qvd (qvd);


You can use the cross table file in the script and use the cross table functionality to amend the script.


Many Thanks

Karthik

knkirubakaran
New Contributor III

Re: Load multiple excel files in Qlikview

Thanks for your response Karthikeyan. But the column b header is different in all the files, so I'm getting error while loading.

Column B header in Excel

Downloads Jun 2016

Downloads Jul 2016

Downloads Aug 2016

balrajahlawat
Esteemed Contributor

Re: Load multiple excel files in Qlikview

Karthikeyan have almost suggested you one of the good solution, just improving his code:

just replace Aug2016/Jul2016/Jun2016 with ' * ' which is a wild char.

Asset:

Load *

From Download -click-*(Excel file);

knkirubakaran
New Contributor III

Re: Load multiple excel files in Qlikview

Getting error while run this script, due to 'column B' header


Asset:

Load *

From Download -clicks * qvd (qvd);

Capture2.PNG

Partner
Partner

Re: Load multiple excel files in Qlikview

pls see attach.

1 create 1 folder and put xlsx file

2.copy path in script in variable v_cale

and reload

Not applicable

Re: Load multiple excel files in Qlikview

Hi,

Sorry for the delay. Is this what you need:

If so, please see the attached qvw and I hope this helps!

Stan