Creating missing data in script

[Anonymous]

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.

[micheledenardi]

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;

[Anonymous]

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

[micheledenardi]

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