topic Re: Load multiple excel files in Qlikview in QlikView

Load multiple excel files in Qlikview

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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:

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

Thanks

Re: Load multiple excel files in Qlikview

harishkumarg — Mon, 26 Sep 2016 18:06:23 GMT

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

Re: Load multiple excel files in Qlikview

Mon, 26 Sep 2016 22:30:43 GMT

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

Re: Load multiple excel files in Qlikview

Anonymous — Tue, 27 Sep 2016 02:55:16 GMT

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

Re: Load multiple excel files in Qlikview

karthikoffi27se — Tue, 27 Sep 2016 04:34:29 GMT

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

Re: Load multiple excel files in Qlikview

Anonymous — Tue, 27 Sep 2016 04:47:31 GMT

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

Re: Load multiple excel files in Qlikview

Anonymous — Tue, 27 Sep 2016 05:31:13 GMT

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);

Re: Load multiple excel files in Qlikview

Anonymous — Tue, 27 Sep 2016 09:59:47 GMT

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

Asset:

Load *

From Download -clicks * qvd (qvd);

Re: Load multiple excel files in Qlikview

florentina_doga — Tue, 27 Sep 2016 13:25:10 GMT

pls see attach.

1 create 1 folder and put xlsx file

2.copy path in script in variable v_cale

and reload

Re: Load multiple excel files in Qlikview

Tue, 27 Sep 2016 23:35:24 GMT

Hi,

Sorry for the delay. Is this what you need:

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

Stan

Re: Load multiple excel files in Qlikview

Anonymous — Wed, 28 Sep 2016 17:16:11 GMT

Thanks Stan and Florentina, it works well now