Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I wondered if you could give me some help on the following : I need to load multiple excel sheets daily into Qview , so I plan to use the files naming convention as :
YYYYMMDD-cost.xls ; YYYYMMDD-hrs.xls ; YYYYMMDD-revenue.xls ; ....etc
Being YYYYMMDD ( YearMonthDate ). I would need to tell the Qlikview script to load all these files, but just from certain ranges , like :
//Multiple files load, file names : YYYYMMDD-hrs.xls
Directory
for ii= 20100101 to 20100131
hrs:
set
LOAD
'DS'
&$(ii) as Dataset,
[Proj. def.]
as Project_Nr,
...etc
ErrorMode=0; D:\00-OC Projects Dashboard\DataSets\hrs_CN47N;The For Each ... Next function let QV cycle through a file list and action those that meet the criteria.
If you define a variable, eg LoadYearMonth as 201003 etc and use this you can limit to finding files with your date range.
Let LoadYearMonth = '201003'
for each myFileName in filelist (..yourpath....&'\' & '$(LoadYearMonth)' & '*.xls)
Load ... FROM '$(myFileName)'
next File
If your already loading you'll be able to fill in the correct formats etc for reading Excel. The reference manual also has doco on For Each.. Next
Thanks for the advice Michael. In fact I haven´t tried it yet , as my first approach was to create a calendar table and then I simply used the For Each ...Next function . I copied all dates from the Calendar table and pasted them in the Load script . I am not sure this is an efficient way for Qview ...but for me it was the easiest to setup ( and so far it works ). Unfortunately I am still on the learning curve using Qview, and discover new things every day .
My script is as follows :
//Multiple files load, file naming : YYYYMMDD-hrs.xls
Directory
D:\00-OC Projects Dashboard\DataSets;for each ii in 20090810,20090811,20090812,20090813,20090814,20090815,20090816,20090817,20090818,20090819,20090820, //....all Year dates from Calendar
hrs:
set
ErrorMode=0;LOAD
$(ii)
as DataSet,[Proj. def.]
as Project_Nr,
[WBS element]
as WBS,
[Activity desc.]
as Activity,
Work
as Plan_hrs,
[Work (A)]
as Actual_hrs,
RemainWrk
as Remain_hrs
FROM
[$(ii)-hrs.xls](biff, embedded labels, table is Sheet1$)where
Work+[Work (A)] > 1;// Work >0 OR [Work (A)]>0; ii