Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
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
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
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
Hi there,
I have also published a blog post on incremental loads that may be of assistance: http://bit.ly/VvY4MN
Steve
Hi Deepakrajan,
Thank u so much............its very helpful to me
Regards
Munirathnam