Daily excel file dataset loading to QlikView - How to set load ranges & Changed Data Capture

[quiquehm]

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 :

20100101 to 20100131 ( so, all days in January )

20100201 to 20100228 ( all days in February )

20100301 to 20100331 ( all days in March )...

......and so on , till December

The problem is the only options I found in the help menu are two control sentences :

For...next

For each ...next

I can´t figure out how to write the script using any of those 2 sentences ( as the filenames will be arranged in these ranges , instead of a continuos list ) , I want Qlikview not to waste time trying to read files like 20100132, 20100133 ...etc ( they will not exist ). I know the set ErrorMode=0 will allow this ( the script will skip those without stopping ) ..but still the script will waste time on that. I would appreciate if you could give me some help on this or direct me to any existing forum post , whitepaper ...etc that could help me.

Also does anybody know how to setup Qview to do Changed Data Capture prior to data loading ? Ideally I would need to setup the load script for these daily files in the best possible efficient way , so Qview does not waste time loading unchanged data from the previous data set ( a day older ). I would like Qview to load only new or changed data. I plan to trend data over time using these daily files loading.

Appreciate any advice

Thanks a lot

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

[michael_anthony]

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

[quiquehm]

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