topic Re: For Each loop with condition on filename in QlikView

For Each loop with condition on filename

danieloberbilli — Tue, 03 Mar 2015 00:11:50 GMT

Dear All,

I have two sets of Excel source files: 'old' and 'new'. I need to load both sets each day. The 'new' set contain additional fields that aren't available in the 'old' set. The filenames contain a datecode that I can use to identify whether a file belongs to old or new and therefore which fields are available.

My idea was to use a For Each loop, save the date-information in a variable (vSingleFile -seems to work) and then use If Then Else to load either the old or the new data table (<3 load old, =3 load new). But my script fails and it seems that my If Then else does not work for whatever reason.

This simplified example has 3 Excel tables: TestFile_1 and TestFile_2 are the old ones and TestFile_3 is the new one. Testfield1 represents the old fields, Testfield2 the new additional fields. Please be aware that I am dealing with hundreds of files.

SET vSourcePath = 'G:\Documents\Desktop\ForEach_FileName\';

SET vSourceFile = 'TestFile_*.xlsx';

SET vSource = '$(vSourcePath)$(vSourceFile)';

FOR Each vPathAndFile in FileList(vSource);

LET vSingleFile = left(SubField(vPathAndFile,'TestFile_',2),1) ;

if $(vSingleFile) < 3 then

LOAD

'old' as Status,

ID,

Testfield1

FROM

$(vSourceFile)

(ooxml, embedded labels, table is Sheet1);

ELSE

LOAD

'new' as Status,

ID,

Testfield1,

Testfield2

FROM

$(vSourceFile)

(ooxml, embedded labels, table is Sheet1);

ENDIF

NEXT vPathAndFile

Thanks a lot in Advance.

Kind Regards

Daniel

Re: For Each loop with condition on filename

danieloberbilli — Tue, 03 Mar 2015 00:17:28 GMT

As alternative I tried to have one table script only with if()-conditions within the table for the new fields like:

if($(vSingleField)=3, Testfield_2, null()) as Testfield_2,

but didnt work neither

Re: For Each loop with condition on filename

danieloberbilli — Tue, 03 Mar 2015 02:11:33 GMT

I realized a silly mistake in the variable used in the From-Load. Now I got a working version: FYI see below

Nevertheless I would be interested if there are other concepts to solve this.

E.g. When simply loading by LOAD * -> Qlik creates a new table for each time the field structure is different and I would need again a loop to concatenate them...so not sure if this would be more efficient.

Just for your interest the working script:

SET vSourcePath = 'G:\Documents\Desktop\ForEach_FileName\';

SET vSourceFile = 'TestFile_*.xlsx';

SET vSource = '$(vSourcePath)$(vSourceFile)';

FOR Each vPathAndFile in FileList(vSource);

LET vSingleFile = left(SubField(vPathAndFile,'TestFile_',2),1) ;

if $(vSingleFile) < 3 then

Old:

LOAD

'old' as Status,

ID,

Testfield1

FROM

$(vPathAndFile)

(ooxml, embedded labels, table is Sheet1);

ELSEIF $(vSingleFile) = 3 then

New:

LOAD

'new' as Status,

ID as ID,

Testfield1,

Testfield2

FROM

$(vPathAndFile)

(ooxml, embedded labels, table is Sheet1);

ENDIF

NEXT vPathAndFile