topic Re: Creating missing data in script in QlikView

Creating missing data in script

Anonymous — Thu, 09 Nov 2017 13:34:47 GMT

Hi all,

I want to perform a caluculation in script itself, By comparing current and later to current day data.

PFA for more info.

Wanna fill those mentioned numbers in excel sheet into highlighted part. with that caluculation.

Re: Creating missing data in script

micheledenardi — Thu, 09 Nov 2017 14:13:49 GMT

This is the result:

And this is the script i've used:

MainTMP:
LOAD AlertStatus,
    Area,
    Filedate,
    ClosedAlerts,
    GeneratedAlerts,
    if(isnull(OpenAlerts) or trim(OpenAlerts)='',peek(GeneratedAlerts)-peek(ClosedAlerts,-2),OpenAlerts) as OpenAlerts,
    peek(ClosedAlerts,-2) as closedCurrent
FROM
 (ooxml, embedded labels, table is [Sheet2 (2)]);

left join(MainTMP)
load
AlertStatus,
date(Filedate-1) as Filedate,
OpenAlerts as OpenAlertsTMP
Resident MainTMP;

left join(MainTMP)
load
'Open' as AlertStatus,
date(Filedate-1) as Filedate,
GeneratedAlerts as GeneratedAlertsTMP
Resident MainTMP
where AlertStatus='Generated';

Main:
NoConcatenate
Load
AlertStatus,
    Area,
    Filedate,
    ClosedAlerts,
    GeneratedAlerts,
    OpenAlerts,
    closedCurrent+OpenAlertsTMP-GeneratedAlertsTMP as NewField
resident MainTMP;

drop Table MainTMP;

Re: Creating missing data in script

Anonymous — Thu, 09 Nov 2017 15:15:29 GMT

Can we make this automated, for huge data. without using peek

Re: Creating missing data in script

micheledenardi — Thu, 09 Nov 2017 15:29:44 GMT

peek() function has no problem with lots of data.... Try it!