topic Re: Data Missing Issue Help ! in QlikView

Data Missing Issue Help !

amit_saini — Mon, 22 May 2017 09:36:50 GMT

Hi Folks,

I'm using below script for incremental load on QVD Generations:

Let vStart = now();

Let vReloadStart = now();

STATION:

LOAD

'$(vPlant)' as Plant,

SQL SELECT

NLFDSTATIONNR,

SSTATIONBEZ,

SSTATIONNR

FROM $(vPlantAutor).STATION;

LET vSTATION_LOAD_DURATION = Interval(now() - vStart) ;

LET vStart = now();

store STATION into $(vQVD)\$(vPlant)_STATION.qvd;

store STATION into $(vQVDBackUp)\$(vPlant)_STATION.qvd;

LET vSTATION_Size = num(filesize('$(vQVD)\$(vPlant)_STATION.qvd')/1024,'00.0') & ' KB';

LET vSTATION_Records = QvdNoOfRecords ('$(vQVD)\$(vPlant)_STATION.qvd');

drop table STATION;

Now we are having very weird situation of missing data (As they are not got updated on time on DB) , so to get rid of this , is there any possibility to implement below logic.

Day 1: incremental load + previous day

Day 2: incremental load + 2 previous day

Day 3: incremental load + 3 previous days

Day 4: incremental load + 4 previous days

Day 5: incremental load + 5 previous days

Day 6: incremental load + 6 previous days

Day 7: incremental load + 7 previous days

If yes then please help me with above "STATION" table on script side.

Thanks in advance!

Regards,

Re: Data Missing Issue Help !

amit_saini — Mon, 22 May 2017 10:14:17 GMT

Any suggestions???

Thanks,
AS

Re: Data Missing Issue Help !

kamal_sanguri — Mon, 22 May 2017 11:16:29 GMT

Concatenate your incremental load with additional load for additional days (yesterday to past seven days).

So, something like

Let vday =1;

Incremental Load

Concatenate

If vday=1 then

Load data

where date = today()-1;

Let vday = $(vday)+1;

elseif vday=2 then

Load data

where date > today()-2;

Let vday = $(vday)+1;

elseif vday=7 then

Load data

where date > today()-8;

Let vday = 1;

Endif

Re: Data Missing Issue Help !

amit_saini — Mon, 22 May 2017 12:14:55 GMT

Hi Kamal,

Thanks!

I will try this and will let u know in cases it's working.

Regards,

Amit

Re: Data Missing Issue Help !

Peter_Cammaert — Mon, 22 May 2017 12:16:28 GMT

As far as I can see, your original script doesn't do an incremental load at all. It always does a full reload because of having no WHERE clause and as a result no restriction to juist the increment.

What data are you lacking in the original script runs?

Re: Data Missing Issue Help !

amit_saini — Mon, 22 May 2017 12:29:01 GMT

Hi Peter,

Sorry! Yes you are right.

Below is an example of incremental load on "STICHPROBE" table:

************************************************************************************************

///$tab STICHPROBE

if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then

trace >>>>>> $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd ;

MAX_TIME:

load

max(DTTSERF) as LAST_LOAD_TIME

from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd)

// where DTTSPROBE >= $(vRefTime) // nicht notwendig

;

let vLAST_LOAD_TIME = timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');

trace >>>>>> LAST_LOAD_TIME: $(vLAST_LOAD_TIME);

drop table MAX_TIME;

else

LET vLAST_LOAD_TIME = '$(vStartDate)';

trace >>>>>> LAST_LOAD_TIME: $(vLAST_LOAD_TIME);

end if

// load new values

STICHPROBE:

LOAD

'$(vPlant)' as Plant,

SQL SELECT

NSPCIDNR,

NTOOLNR,

NSTPNR,

NPERS_IDNR,

NLFDSTATIONNR,

NLFDMASCHNR,

NLFDLINIENR,

DTTSPROBE,

DTTSERF,

NSTPFEHLERFLAG,

DXMIN,

DTO,

DTU,

DXQ,

SZUSINFO1,

SZUSINFO2,

SZUSINFO3,

SZUSINFO4,

SZUSINFO5,

SZUSINFO6,

SZUSINFO7,

SZUSINFO8,

SZUSINFO9

FROM $(vPlantAutor).STICHPROBE

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

// load prior values

if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then

concatenate(STICHPROBE)

load

from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd);

end if

// store all values

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

LET vSTICHPROBE_LOAD_DURATION = Interval(now() - vStart) ;

LET vStart = now();

LET vSTICHPROBE_Size = num(filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd')/1024,'00.0') & ' KB';

LET vSTICHPROBE_Records = QvdNoOfRecords ('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd');

//********************

drop table STICHPROBE;

***********************************************

Actually we have some problem in updating DB , so some data instead of today will be getting updated on Thus (Let's Say) and doing incremental load alone will not work to show full data.

Hope you understood!

Thanks,

Re: Data Missing Issue Help !

Peter_Cammaert — Mon, 22 May 2017 13:08:44 GMT

Off the top of my head, here are two suggestions:

You can use the QvdCreateTime() function the define the cutoff point for your increment. Use Floor(QvdCreateTime()) to create a WHERE clause for the increment, like in

IF Not IsNull(QvdCreateTime(YourQVDFilePath)) THEN
LET vWHERE = "WHERE DTTSERF >= '" & date(Floor(QvdCreateTime(YourQvdPath)), 'DD.MM.YYYY') & "'" ;
ELSE
LET vWHERE = '';
END IF
The second suggestion is about the cutoff date itself. IMHO it's more safe to ignore the last date in the historical QVD as there may be other rows added to your DB table on the day that you extract your increment. These wil be liost if you only load rows that are younger than the last day in the QVD. Or translated to your situation, add rows from the previous QVD file that are older than the cutoff date, not older or equal to that date. So add a WHERE clause to your CONCATENATE LOAD that is identical to the one specified earlier, except that the relational opperater is < (Less than) instead of >= (greater or equal than). You can use something like this:

IF (len(trim(vWHERE)) > 0) THEN // Historical QVD exists
CONCATENATE (STICHPROBE) LOAD *
FROM [YourQvdPath] (qvd)
WHERE DTTSERF < date(Floor(QvdCreateTime(YourQvdPath)), 'DD.MM.YYYY');
END IF

Best,

Peter