For.. each / or loop?

[joris_lansdaal]

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

[swuehl · Accepted solution]

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.

[joris_lansdaal]

‌thanks Stefan.