topic Re: Last execution date in QlikView

Last execution date

dseelam — Wed, 02 Nov 2016 15:34:31 GMT

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

Re: Last execution date

sunny_talwar — Wed, 02 Nov 2016 15:44:42 GMT

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

Re: Last execution date

johnw — Wed, 02 Nov 2016 17:36:02 GMT

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.

Re: Last execution date

dseelam — Wed, 02 Nov 2016 19:07:29 GMT

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

Re: Last execution date

dseelam — Wed, 02 Nov 2016 19:10:43 GMT

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!

Re: Last execution date

sunny_talwar — Wed, 02 Nov 2016 19:13:33 GMT

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

Re: Last execution date

dseelam — Wed, 02 Nov 2016 19:26:19 GMT

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

Re: Last execution date

sunny_talwar — Wed, 02 Nov 2016 23:16:49 GMT

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