3 Replies Latest reply: Mar 3, 2017 6:51 AM by vir vir RSS

    for loop with file exist function in qlik....

    vir vir

      In our project we having 5 files with name like:

       

       

      C:\HexaProject\SourceDocuments\Inputfiles\DirectBooking20170101

      C:\HexaProject\SourceDocuments\Inputfiles\OnlineBooking20170101

      C:\HexaProject\SourceDocuments\Inputfiles\CallBooking20170101

      C:\HexaProject\SourceDocuments\Inputfiles\Reference20170101

      C:\HexaProject\SourceDocuments\Inputfiles\Cancelled20170101

      Every day we need to check these files exist or not …

       

       

      If exist than we need to load these files as a concatenated solution in 1 table.

      but we need to check the availability of all files if any one file is available than table will load .......

       

       

      [Booking Final]:

      Booking_wid,

      Booking_Type,

      Payment_Method

      From “Path”….

       

       

      Kindly suggest me the way to load these files while using For loop and we need to check each files presence also before doing concatenation……please suggest.

        • Re: for loop with file exist function in qlik....
          Miguel Angel Baeyens de Arce

          Easiest way:

           

          LET vInputFileDate = Date(Floor(YearStart(Today())), 'YYYYMMDD');

           

          LOAD *

          FROM C:\HexaProject\SourceDocuments\Inputfiles\*$(vInputFileDate)

          (options and delimiters here);

           

          Note that 20170101 can also be the result of a variable as set above, and that will load whatever file in the folder meets the "*$(vInputFileDate)" criteria.

           

          If you must check whether a single file exist and then load it, then something like this or this would do the job.

            • Re: for loop with file exist function in qlik....
              vir vir

              i have tried your scenerio ...but is is only adding 1st file .....

              not loading all file......please suggest

               

              and here vToday id daily date which we need to pick as code is not bounded for particular date

              ////////////////////////////////////////////

              Directory D:\QlikView\SourceDocuments\InputFiles;

              for Each csvfile in filelist ('$(vCSVFilePath)*$(vToday).csv')

               

               

              LOAD CentralBookingRef,

                   Source,

                   Quantity,

                   Amount,

                   dateBooked,

                   Description,

                   ArrivalDate,

                   EMail,

                   Title,

                   FirstName,

                   Surname,

                   Addr1,

                   Addr2,

                   Addr3,

                   Town,

                   County,

                   Postcode,

                   Phone,

                   Flag1,

                   Flag2,

                   Site

                 

              FROM

              $(csvfile)

              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

               

               

              NEXT

               

              //////////////////////////////////////////

            • Re: for loop with file exist function in qlik....
              Jakob Hansen

              something like this

               

              for each file in filelist('C:\HexaProject\SourceDocuments\Inputfiles\*.*')

               

              if filetime($(file))>0 then

               

              load

              *

              from $(file)

              ;

              end if

              next