topic Incremental Load in QlikView

Incremental Load

Wed, 16 May 2012 22:18:01 GMT

Hi,

I posted this in the newbie forum but haven't got a response so far. So, I am posting here. Sorry if this is still need to go to the newbie forum.

I am trying to load data greater than a certain date as incremental to the previous load.

The step I followed is

1. Stored the previous load as a qvd

2. Load the previous qvd

3. Get max date from previous qvd

4. Load the new data (csv file) where date is greater than max date from previous loaded qvd file.

I am getting an error field <vMax> not found. Below is the script. Please note I had copied codes from posts related to incremental load so not sure if there is a better and easy way to do what I am trying to do.

Thanks,

Deepak

Temp:
LOAD * from Sales_Fact.qvd (qvd); /*This qvd has data as of week 4-14-12) */
Res:
LOAD Max(%_Day_Date_ID) as MaxDate
RESIDENT Temp;
let vMax=peek('MaxDate',-1,'Temp');

Directory;
Sales_Fact:
LOAD TEXT(Terr_id) as %_Terr_ID,
     TEXT(Brand) as %_Brand_ID,
     DATE(w_date) as %_Day_Date_ID,
     NUM(sales) as FCT_sales

FROM
[sales_inputs - we4-20-12.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
WHERE
DATE(w_date) > vMax;

Concatenate

LOAD * from Sales_Fact.qvd (qvd);

Incremental Load

Wed, 16 May 2012 22:53:43 GMT

Hi deepak,

Try this:

Go to "settings"-> "Document Properties-> "triggers" -> "on post reload"-> add action->"add"-> "external" -> set variable

Now in this variable set something like this vMaxDate= max(DATE_FIELD).

Use this variable in your load script for the max date.

Only difference, in this case you will have to reload the application twice.

On the first attempt, max date will be stored in this variable, in the next reload it will be utilised as the incremental load criteria.

Hope this helps.

Bikash

Re: Incremental Load

rwunderlich — Thu, 17 May 2012 06:55:09 GMT

You need to add $() around the variable reference:

WHERE DATE(w_date) > $(vMax)

You may also want to try the incremental load routines provide by http://qlikviewcomponents.org

-Rob

http://robwunderlich.com

Re: Incremental Load

Thu, 17 May 2012 15:08:29 GMT

Rob,

Thanks for your solution which was spot on. Identifying the variable with $ sign did it. Further, I needed to drop the temp tables to make it work right.

Thanks Bikash for your idea. I never had to use it as it was in the end a minor issue with referencing variable names.

Best,

Deepak

Re: Incremental Load

stevedark — Fri, 25 Jan 2013 19:53:51 GMT

Hi there,

I have also published a blog post on incremental loads that may be of assistance: http://bit.ly/VvY4MN

Steve

http://www.quickintelligence.co.uk/

Re: Incremental Load

rathnam_qv — Sat, 06 Jul 2013 14:10:30 GMT

Hi Deepakrajan,

Thank u so much............its very helpful to me

Regards

Munirathnam