Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
In the process of implementing Incremental loading
if I am using Max (cancelled date) to incremental load, in this process I found a scenario where I can miss some data if I am not reloading this DB every day
Is there a way I can create a last execution date or time and use it instead of Max (cancelled date) so that I can reload my DB every week or so ?
Thanks in advance
You can reload all data from the last n days instead of doing based on the max cancelled date. This can be done by assigning a value based on Today() - n instead of using Max([cancelled date])
LET vVar = Date(Today() - 7); -> For pulling all data which got modified in last 7 days
In most of my incremental loads, I pull in more than the minimum amount of data. Like if I have a daily load, I might load the three most recent days of data. I only get in trouble if somehow it fails to load several days in a row, and that's something I pay attention to, plus all my incremental loads do a full reload on a periodic basis. A daily incremental load might do a full reload on a Sunday, say.
So for a database you want to reload into QlikView weekly, I'd probably pull in, I don't know, the last 30 days of data? I wouldn't try to detect the last execution date and time. I wouldn't try to find the max of any particular timestamp in the data. I'd just load 30 days of data and call it good. Well, in combination with a periodic full reload, like once every month or two.
I'm unclear, though, how you're having a problem using max([Cancelled Date]), assuming that Cancelled Date is a valid field for incrementally loading on. Well, assuming you're loading >= max([Cancelled Date]), anyway. If you don't run for a few days, I'd think that logic would then pick up those few days of missed data. Run weekly, and you'd pick up the last week's data.
As far as the last execution date and time, there's a reloadtime() function that might work for you. I believe if you reference it during the script, it's the time of the previous reload, not the current reload, but you'd want to make sure.
Hey Sunny,
All I ma trying to do is to use
SQL SELECT *
FROM APPS."XXHSP_DB_CANCELLED_LINES_V" where CANCELLED_DATE > #$(LastExecTime)# AND CANCELLED_DATE < $(ThisExecTime);
but it is throwing me an error
Hey John,
I appreciate your explination
but All I want to do is instead of increment data by mac cancelled date I want to use last reload time or last exc time depending on cancelled date, so that I will have more accurate data
Thanks!
Didn't we resolve this in the morning already?
SQL SELECT *
FROM APPS."XXHSP_DB_CANCELLED_LINES_V" where CANCELLED_DATE > #$(LastExecTime)# AND CANCELLED_DATE < TO_DATE('$(ThisExecTime)', 'MM/DD/YYYY');
Where you create ThisExecTime like this
LET ThisExecTime = Date(Floor(......));
Sunny last time we used last updated date as max cancelled date
Now I need is to use Last exc time instead of date
am using Let ThisExecTime = Date(Floor(Now( ))); & Let LastExecTime = ThisExecTime;
still facing error after using
FROM APPS."XXHSP_DB_CANCELLED_LINES_V" where CANCELLED_DATE > #$(LastExecTime)# AND CANCELLED_DATE < $ TO_DATE('$(ThisExecTime)','MM/DD/YYYY');
Sorry, I did not realize that the variable was getting used twice
FROM APPS."XXHSP_DB_CANCELLED_LINES_V" where CANCELLED_DATE > TO_DATE('$(ThisExecTime)','MM/DD/YYYY') AND CANCELLED_DATE < TO_DATE('$(ThisExecTime)','MM/DD/YYYY');