Creating the missing data

[HirisH_V7]

Hello all,

Here in the below table, i need to generate the missed data for (yellow highlighted ).

Generate - closed  alerts of current day.

How can i acheive this in script?

PFA sample FYR.

[Anonymous]

what is the value of this field in OpenAlert??? 16?? from where this value comes?

[HirisH_V7]

Its generated alerts- closed alerts , 47-31 =16. (same date data)

[YoussefBelloum]

Hi,

when exactly do you will have empty values for this column?

you need to know when exactly or what are the conditions to have an empty cell there.

and you need to do it with a IF on the script, with something like this

TABLE:

LOAD

          x,

          x,

          if(column1='something' AND/OR column2='something', THE VALUE YOU WANT TO INSERT', 'OpenAlerts) as OpenAlerts

FROM TABLE... ;

[antoniotiman]

May be this

SET DateFormat='DD-MM-YYYY';
Temp:
LOAD AlertStatus,
Area,
Filedate,
ClosedAlerts,
GeneratedAlerts,
OpenAlerts
FROM
[Sample.xlsx]
(ooxml, embedded labels, table is [Sheet2 (2)]);
NoConcatenate
LOAD AlertStatus,Area,Filedate,ClosedAlerts,GeneratedAlerts,
If(AlertStatus = 'Open',RangeSum(Peek(GeneratedAlerts),-Peek(ClosedAlerts,-2)),OpenAlerts) as OpenAlerts
Resident Temp
Order By Filedate,Area;
Drop Table Temp;

[HirisH_V7]

It should not empty, so we need to check for each date that it is having some number.

&

Yeah we can right if condition, But how we will pick particular date for that field.

[techvarun · Accepted solution]

Try below script

TEST:
LOAD * INLINE [
    AlertStatus, Area, Filedate, ClosedAlerts, GeneratedAlerts
    Closed, A, 08-11-2017, 30, 0
    Generated, B, 08-11-2017, 0, 43
    Open, C, 08-11-2017, 0, 0
    Closed, A, 09-11-2017, 31, 0
    Generated, B, 09-11-2017, 0, 47
    Open, C, 09-11-2017, 0, 0
    Closed, A, 10-11-2017, 32, 0
    Generated, B, 10-11-2017, 0, 50
    Open, C, 10-11-2017, 0, 0
];


JOIN
Load Sum(GeneratedAlerts)- Sum(ClosedAlerts) as OpenAlertstest, Filedate resident TEST Group by Filedate;




LOAD *, if(AlertStatus ='Open', OpenAlertstest, 0) as OpenAlerts Resident TEST;
Drop Table TEST;
DROP FIELD OpenAlertstest;

[techvarun]

[HirisH_V7]

Hi Varun,

Please check the attached a mod in the caluculation of open alerts,

Closed alerts Current day+Open alerts later day-alerts generated later day

now the caluculation should pick later date and current date.