2 Replies Latest reply: Jun 11, 2016 6:29 AM by Joris Lansdaal RSS

    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

        • Re: For.. each / or loop?
          Stefan Wühl

          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.