Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Author

Hi,

Sorry for the delay. Is this what you need:

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

Stan

View solution in original post

10 Replies
harishkumarg
Creator III
Creator III

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
Author

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

Anonymous
Not applicable
Author

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

karthikoffi27se
Creator III
Creator III

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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


Asset:

Load *

From Download -clicks * qvd (qvd);

Capture2.PNG

florentina_doga
Partner - Creator III
Partner - Creator III

pls see attach.

1 create 1 folder and put xlsx file

2.copy path in script in variable v_cale

and reload

Not applicable
Author

Hi,

Sorry for the delay. Is this what you need:

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

Stan