For Each loop with condition on filename

[danieloberbilli]

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

[danieloberbilli]

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

[danieloberbilli · Accepted solution]

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