2 Replies Latest reply: Jan 26, 2016 5:03 AM by Hendrik Faurie RSS

    Incremental Load with dynamic file names

    Hendrik Faurie

      Hi experts!

       

      Still relatively new to qlikview so bare with me...

      I am trying to set up an incremental load to shorten my load times of my qvd's.

      On the machine itself files are placed every day with file names like data160121 where the last 6 digets resemble the date. Now in this folder there are at least 100 similar files dating back 3 months. EACH file contains roughly 500 000 records. So I would like qlikview to look at the file name and load according to that rather than the data inside each file as it would have to cycle through all that data each day which is as you could imagine not an option at all...

       

      I assume when at the Load "FROM" part of the load statement you can't just use something like FileBaseName()? It seems like it can only look at the file data itself and not at the file name...

       

      I was thinking of using something like this:

      -> a variable called vLastReload which gets the last 6 digits from the last reloaded file. example:

      vLastReload= 160121 //As per above mentioned file

       

      And at the end of the load statement:


      Concatenate(BaseDataFile.qvd) //Loaded earlier

      Load

           Field1,

           Filed2,

           Field3

      From

      'D:\DailyDataFolder\data*.exl'

      (fix, codepage is 1252) where (right(FileBaseName(),6))<$(vLastReload);

       

      If I am missing key theory somewhere an explanation would be most welcome! I have tried reading most of the sources relating to incremental loads both on the community and from what Mr. stevedark has written, although the articles were most informative I could not find exactly what I was looking for... (Or I did not understand that which I was looking at WAS what I was looking for)

      The files I am trying to load here are just some of the daily files I need to load into a qvw. The others work in a similar way and have similar no. of records. So getting this just right is extremely important in the long run.


      Thanks in advance!!


      Kind Regards

        • Re: Incremental Load with dynamic file names
          Jonathan Dienst

          No, that won't work - even if it did, it would have to load each file to get the FileBaseName() - that command only works inside a load.

           

          Rather use

           

               For Each vFile in FileList('D:\DailyDataFolder\data*.exl')

                    If Right(SubField(vFile, '.', 1), 6) > vLastReload Then

           

                         Concatenate([BaseDataFile.qvd])

                         LOAD *

                         FROM [$(vfile)]

                         (fix, codepage is 1252);

           

                    End If

               Next

           

          While this code loops over the filenames, it does not need to open the files that are not required.

            • Re: Incremental Load with dynamic file names
              Hendrik Faurie

              Thank you kindly for your swift response.

              It works perfectly! I've seen similar code but they all looked inside the files which was exactly the thing I wanted to avoid.

              Is there an article or a post somewhere I could read that explains the

              For Each vFile in FileList('D:\DailyDataFolder\data*.exl')

              part of the code? The help section in QlikView just doesn'y make sense to me... I'd just feel a bit better if I understand what happens in the background rather than just copy and paste and be glad it works... This is certainly something I will use a lot in the future.


              Again thank you for the neat solution.