topic Full day reload help ??? in QlikView

Full day reload help ???

amit_saini — Mon, 22 Feb 2016 11:33:15 GMT

Hi Folks,

I'm using below script for generating QVD over the weekends for the full week , in case I have missed the data for any weekday!

Let vLoadFrom = timestamp(If(Weekday($(vLAST_LOAD_TIME)) >= 5, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)), 'DD.MM.YYYY HH24:MI:SS');

STICHPROBE:

LOAD

'$(vPlant)' as Plant,

SQL SELECT

NSPCIDNR,

NTOOLNR,

NSTPNR,

NPERS_IDNR

FROM $(vPlantAutor).STICHPROBE

WHERE DTTSERF > to_timestamp('$(vLoadFrom)', 'DD.MM.YYYY HH24:MI:SS');

store STICHPROBE into $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd;

Now what I'm looking for is :

Suppose when I have triggered QVD Generator @ 6:00 AM , this might have missed some data which was made available later this day@9:00 PM inside database. So to support these cases of database update issue , is there any solution with the above script where I can write :

"Each day @ 11:00 PM , I want a full load for fetching data from database for the full day and store as QVD"

Please suggest if this approach is possible or not???

Thanks,

Re: Full day reload help ???

Gysbert_Wassenaar — Mon, 22 Feb 2016 16:16:36 GMT

Something like this:

If Frac(Now(1)) >= 23/24) Then

// do full reload

Else

// do incremental load

Endif

Re: Full day reload help ???

amit_saini — Tue, 23 Feb 2016 04:15:33 GMT

Thanks Gysbert!

But could you please help me with complete script taking "STICHPROBE" as an example.

Thanks,
AS

Re: Full day reload help ???

tresB — Tue, 23 Feb 2016 05:11:16 GMT

While you pass a qv variable to SQL engine, the evaluation of variable still gets done by QV and the resultant is sent to SQL engine. That means, your variable definition:

Let vLoadFrom = timestamp(If(Weekday($(vLAST_LOAD_TIME)) >= 5, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)), 'DD.MM.YYYY HH24:MI:SS');

is supposed to be evaluated by QV. But the time stamp format 'DD.MM.YYYY HH24:MI:SS' is unknown to QV. So you don't get the desired output.

Hope this helps.