Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
danieloberbilli
Specialist II
Specialist II

For Each loop with condition on filename

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

1 Solution

Accepted Solutions
danieloberbilli
Specialist II
Specialist II
Author

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

View solution in original post

2 Replies
danieloberbilli
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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