Incremental Load

[#1]

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);

[#2]

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

[rwunderlich · Accepted solution]

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

[#4]

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

[stevedark]

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/

[rathnam_qv]

Hi Deepakrajan,

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

Regards

Munirathnam