topic Re: Incremental Load with dynamic file names in QlikView

Incremental Load with dynamic file names

splitcore — Mon, 25 Jan 2016 06:34:24 GMT

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

jonathandienst — Tue, 26 Jan 2016 05:45:59 GMT

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

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

splitcore — Tue, 26 Jan 2016 10:03:48 GMT

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.