Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Sorry for the delay. Is this what you need:
If so, please see the attached qvw and I hope this helps!
Stan
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
I'm not understanding what your goal is. Can you please restate?
I need to create Cross table based on the attached excel sheets. It comes monthly with incremental load.
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
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
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);
Getting error while run this script, due to 'column B' header
Asset:
Load *
From Download -clicks * qvd (qvd);
pls see attach.
1 create 1 folder and put xlsx file
2.copy path in script in variable v_cale
and reload
Hi,
Sorry for the delay. Is this what you need:
If so, please see the attached qvw and I hope this helps!
Stan