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



      In three locations:




      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


      LOAD  *

           'POS_Salesline' as POS_TYPE_2,

           FileBasename() as POS_Filename,

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



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

      Where Productboekingsgroep='PB_POS' and




      //And One for Return_Lines.




           'POS_Returnline' as POS_TYPE_2,

           FileBasename() as POS_Filename,

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



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

      Where Productboekingsgroep='PB_POS'and




      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.