topic Creating the missing data in QlikView

Creating the missing data

HirisH_V7 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Creating the missing data

Anonymous — Thu, 09 Nov 2017 10:12:50 GMT

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

Re: Creating the missing data

HirisH_V7 — Thu, 09 Nov 2017 10:16:24 GMT

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

Re: Creating the missing data

YoussefBelloum — Thu, 09 Nov 2017 10:19:23 GMT

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

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

FROM TABLE... ;

Re: Creating the missing data

antoniotiman — Thu, 09 Nov 2017 10:29:26 GMT

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;

Re: Creating the missing data

HirisH_V7 — Thu, 09 Nov 2017 10:31:16 GMT

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.

Re: Creating the missing data

techvarun — Thu, 09 Nov 2017 10:33:35 GMT

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;

Re: Creating the missing data

techvarun — Thu, 09 Nov 2017 10:36:17 GMT

Re: Creating the missing data

HirisH_V7 — Thu, 09 Nov 2017 11:28:50 GMT

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.