Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
bunnyv123
Creator II
Creator II

Creating missing data in script

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.

3 Replies
micheledenardi
Specialist II
Specialist II

This is the result:

2017-11-09 15_13_10-QlikView x64 Personal Edition - [C__Users_Michele.Denardi_Desktop_test2.qvw_].png

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;

bunnyv123
Creator II
Creator II
Author

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

micheledenardi
Specialist II
Specialist II

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