topic Re: Incremental Load in QlikView

Incremental Load

Anonymous — Tue, 02 Jan 2018 07:08:10 GMT

Hi All,

I want to Implement Incremental Load for Below Scenario,

On daily Basis i will receive the new CSV file,i want to pick up the recent CSV file (Coding has been Done for this part)
If CSV file as new data or updated data Qlikview want to display new and updated record ,otherwise it will want to display the old data fetched from the previous CSV file

Date Column used is: Date_Update

Thanks in Advance,

Re: Incremental Load

Anil_Babu_Samineni — Tue, 02 Jan 2018 07:15:20 GMT

How is the CSV file names looks like in your folder?

Re: Incremental Load

chris_andersson — Tue, 02 Jan 2018 07:25:09 GMT

What type of incremental load would you say you are implementing? Should QV display old data from the previous CSV-file even if there's new data from the latest CSV?

Reference:
Using QVD files for incremental load ‒ QlikView

Re: Incremental Load

Anonymous — Tue, 02 Jan 2018 07:39:53 GMT

For Getting Recent file from the Folder:

FOR EACH file1 in FileList('$(vFilePath2)*.csv'); // Loops each excel file in the given Folder

LET vYears1 = mid(file1,26,28);

LET vDates1 = mid(vYears1,15,14);

LET vCSVData = Rangemax(vDates1,vCSVData); // Gets the max year for every iteration

Next

CSVdata File Format=CSV_Data_20170316032409

Vfilepath2=Holding the path location where CSV data will get downloaded on daily basis from SAP system.

Re: Incremental Load

Anonymous — Tue, 02 Jan 2018 07:42:20 GMT

need to display the old data from previous CSV file and New and updated record from the New CSV file.

Re: Incremental Load

Anil_Babu_Samineni — Tue, 02 Jan 2018 07:57:07 GMT

If this is from SAP basis, Then good to know.

Anyway, To incr. You could try this

FOR EACH file1 in FileList('$(vFilePath2)*.csv'); // Loops each excel file in the given Folder

LET vYears1 = mid(file1,26,28);

LET vDates1 = mid(vYears1,15,14);

LET vCSVData = Rangemax(vDates1,vCSVData); // Gets the max year for every iteration

Next

Yours:

LOAD Date_Update, Another;

Store Yours into [FilePath...];

Drop Table Yours;

Sample:

Load * From Yours;

Last_Update_Reload:

Load Max(Date_Update) as Max_Date Resident Sample;

Let VarForMax = Peek('Max_Date', 0, 'Last_Update_Reload');

Drop Table Sample;

First:

Load * From Sample_External_Source Where Date_Update > $(VarForMax);

Concatenate

Load * From <Sample Qvd Path>;

Store First into ...;

Drop Table First;

Load * From <First>;

Re: Incremental Load

chris_andersson — Tue, 02 Jan 2018 08:28:23 GMT

Will you be able to use QVD files to store the Incremental load?

From Qlik Help:

Re: Incremental Load

Anonymous — Tue, 02 Jan 2018 08:29:56 GMT

i will try and ping u if i need any clarification

Re: Incremental Load

Peter_Cammaert — Tue, 02 Jan 2018 10:42:47 GMT

It all starts with "how do you identify your rows in your data source"? The technical question would be: what is the primary key of your rows.

If you have a primary key, it would simply be a matter of reading the latest CSV file in its entirety, and then adding rows from all older CSV files that you haven't loaded already. The new ones will be loaded, the updated ones will be loaded and not overwritten by older ones, and the old ones will be loaded only if there are no newer copies available in the internal table.

As suggested by Christoffer Andersson, it would be very interesting if you could save the current internal table as a QVD containing the historical data at the end of your script. Next time you reload, you only load from a new CSV (newer than your last reload) and add historical data from the QVD. Performance will be much better.

Best,

Peter

Re: Incremental Load

Anonymous — Thu, 04 Jan 2018 05:48:57 GMT

Hi I am Facing Error: Garbage in your Statement after Where Date_Update > $(VarForMax)

Re: Incremental Load

Anil_Babu_Samineni — Thu, 04 Jan 2018 05:50:52 GMT

May be cover with single quote for that variable