Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

HirisH_V7
Honored Contributor

Creating the missing data

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.

HirisH
“Aspire to Inspire before we Expire!”
1 Solution

Accepted Solutions
techvarun
Valued Contributor II

Re: Creating the missing data

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;

8 Replies
Highlighted
thiago_pinho
New Contributor III

Re: Creating the missing data

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

HirisH_V7
Honored Contributor

Re: Creating the missing data

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

HirisH
“Aspire to Inspire before we Expire!”
YoussefBelloum
Esteemed Contributor

Re: Creating the missing data

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
Honored Contributor III

Re: Creating the missing data

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
Honored Contributor

Re: Creating the missing data

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.

HirisH
“Aspire to Inspire before we Expire!”
techvarun
Valued Contributor II

Re: Creating the missing data

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
Valued Contributor II

Re: Creating the missing data

sample.PNG

HirisH_V7
Honored Contributor

Re: Creating the missing data

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.

HirisH
“Aspire to Inspire before we Expire!”