Incremental Load in Qlikview

[s10157754]

Dear Qlikview Experts,

I would like to seek help from you on writing script for incremental load in Qlikview. I would just like to load the new data file into my QVD everyday instead of loading all the previous data again. So it will save the user a lot of time when dealing with huge amount of raw data in future. (I have saw quite a number of incremental load examples, but I have no idea on how to continue it from my current script) Any ideas from you all would be greatly appreciated!

Basically, I have 3 Machines: 'Machine109', 'Machine149' and 'Machine162' respectively. Everyday these 3 Machines' data will be created in txt file format and store in a folder called "DataBase". I have already wrote a script to extract the raw data from this "DataBase" folder. So when user reload the QVD, it will automatically loop through all the txt files in that folder.

(Everyday there will be 1 new txt file added for different Machines. The name of the txt file will stays the same except the date will be changing.) E.g:

Old txt file : Machine_109_03rd_Aug_2017

New txt file: Machine_109_04th_Aug_2017

Attached "DataBase" Folder and my QVD for your reference. Thanks a lot for your precious time! ! !

Best Regards

Qian Ning

[s10157754]

swuehl‌

[swuehl]

Concerning the general incremental approach, just follow the many existing example, like

Using QVD files for incremental load ‒ QlikView

In your case, the only special treatment needed is the handling of your data source, text files, with the update date encoded in the file name. So you would need to parse the file name to extract machine number and date and store this in your resulting table / QVD.

When looking for new files, you need to determine the max date in your QVD (or maybe just Today(), if you plan for a daily reload and file delivery.

Now, when getting all filenames from your folder, skip the files with an older file date / date in file name.

Alternatively, move all files already consumed to another folder, so you have only new files in the folder to read in.

[s10157754]

Dear Stefan,

Thanks for your information. I did work on it for a couple of days and I had also followed some of the samples online(Including the one you mentioned above).

However, I had faced difficulties on implementing the solution and add on to my current script. Could you provide me with more detailed information on how to write the script? Thanks a lot for your precious time!

Best Regards

Qian Ning

[Clever_Anjos]

PFA attached a possible solution.

It creates a control.qvd that stores which and when files  have been loaded.

It won´t load a file twice

[s10157754]

Dear Anjos,

Thanks for your script! It looks like working properly. However, After I change my directory path and run your script, there is no any data showing in my qilkview dashboard. May I know what is the problem behind? Thanks for your time!

Best Regards,

Qian Ning

[Clever_Anjos]

This app stores a final qvd into your "Database" folder.

You can load it after all "calls"

[s10157754]

Dear Anjos,

I did tried to add a new txt file into one of my machine folders in database, and the script execute correctly which only shows the newly added txt file. However, when I was trying to load it after all my "calls", I realised that the "SummaryTable.QVD" was only showing the newly added data and all the history data were not included. The picture below shows the situation I was facing now.

Hope to hear from you soon!

Best Regards,

Qian Ning

[Clever_Anjos · Accepted solution]

Please change to this code below and remove control.qvd and SummaryTable.qvd

SUB Scanfolder(Root)

let ControlQVD = left(Root,Index(Root,'\',-1))&'control.qvd';

Let SummaryQVD = left(Root,Index(Root,'\',-1))&'SummaryTable.qvd';

If FileSize(ControlQVD) then

control:load filename, timestamp from [$(ControlQVD)](qvd);

else

control:load '' as filename, now() as timestamp AutoGenerate 0; // EmptyTable

endif

For each FileExtension in 'txt'

For each FoundFile in FileList(Root &'\Machine_???_????_???_2017.'& FileExtension)

Let t = alt(FieldIndex('filename','$(FoundFile)'),0);

if  t = 0 then

control: load '$(FoundFile)' as filename, now() as timestamp AutoGenerate 1; // Keeps record of this file;

SummaryTable:

LOAD @1 as Date,

    @2 as Time,

    @3 as ProductNo,

    @4 as ProductModel,

    @5 as Result,    

    SubField(FileName(), '_', 2) as MachineNo,

    SubField(FileName(), '_', 3) as Day,

    SubField(FileName(), '_', 4) as Month,

    SubField(FileBaseName(), '_', 5) as Year  

FROM [$(FoundFile)]

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

endif

  Next FoundFile

Next FileExtension

if NoOfRows('control') > 0 then // table has been incremented?

store control into [$(ControlQVD)](qvd);

drop Table control;

Endif

if NoOfRows('SummaryTable') > 0 then // table has been incremented?

If FileSize('$(SummaryQVD)') > 0 then

Concatenate(SummaryTable) load * from [$(SummaryQVD)](qvd);

Endif

store SummaryTable into [$(SummaryQVD)](qvd);

drop Table SummaryTable;

endif

End Sub

[s10157754]

Dear Anjos,

Sorry for the inconvenience caused to you! My own mistake which disabled the field from displaying. Your script works perfectly for me! Thank you so much for your precious time!

Best Regards,

Qian Ning