topic For.. each / or loop? in QlikView

For.. each / or loop?

joris_lansdaal — Sat, 11 Jun 2016 07:57:54 GMT

Hi all,

I want to improve my script in readabilty and efficiency; it is working.

I have two types of files

ABND_SalesLines_DD-MM-YYYY.xlsx

ABND_ReturnLines_DD-MM-YYYY.xlsx

In three locations:

$(vDataPath01)

$(vDataPath02)

$(vDataPath002)

Currently I have below script three times (one for every datapath). Can anyone tell me how to improve this? Am i looking for:

For..each or a loop?

//One for Sales_Lines

POS_TEMP:

LOAD *

'POS_Salesline' as POS_TYPE_2,

FileBasename() as POS_Filename,

Right(FileBasename(),Len(FileBaseName())-16) as POS_File_Date

FROM

[$(vDataPath01)\ABND_SalesLines*.xlsx]

(ooxml, embedded labels, table is [Sales lines])

Where Productboekingsgroep='PB_POS' and

Boekingsdatum>=MonthStart(AddMonths(Right(FileBasename(),Len(FileBaseName())-16),-1))and

Boekingsdatum<=Today()-2;

//And One for Return_Lines.

Concatenate

LOAD

'POS_Returnline' as POS_TYPE_2,

FileBasename() as POS_Filename,

Right(FileBasename(),Len(FileBaseName())-17) as POS_File_Date

FROM

[$(vDataPath01)\ABND_ReturnLines*.xlsx]

(ooxml, embedded labels, table is [Return lines])

Where Productboekingsgroep='PB_POS'and

Boekingsdatum>=MonthStart(AddMonths(Right(FileBasename(),Len(FileBaseName())-17),-1))and

Boekingsdatum<=Today()-2;

Thanks Joris

Re: For.. each / or loop?

swuehl — Sat, 11 Jun 2016 10:05:44 GMT

I would probably use two nested FOR EACH .. IN loops

(one to iterate over the list of your three data paths, one to iterate over the FILELIST('*.xlsx'), which has not so many issues as the star symbold wildcard in the FROM statement).

There is a code sample in the HELP for FOR EACH .. IN how to get the syntax.

Within the loops, you can also set variables for the POS_FILE_DATE and POS_TYPE which you can then use in the LOAD statement, so I think you can essentially burn it down to two nested FOR EACH .. IN loops and a single LOAD statement.

Re: For.. each / or loop?

joris_lansdaal — Sat, 11 Jun 2016 10:29:12 GMT

‌thanks Stefan.