topic Re: Plz help with grouping data based on column in QlikView

Plz help with grouping data based on column

Tue, 21 Jul 2015 14:01:38 GMT

I have following data

CHECKED AT, System NAME, NAME, STATUS

7/16/2015 9:25:02 AM,XX,A,"Running"

7/16/2015 9:25:02 AM,XX,B,"Running"

7/16/2015 9:25:05 AM,YY,"A","Running"

7/16/2015 9:25:04 AM,YY,"B","Stopped"

7/16/2015 9:25:05 AM,ZZ,"A","Stopped"

7/16/2015 9:25:04 AM,ZZ,"B","Stopped"

Here in this i have 3 system for which i have status of A and B separated, if both A and B are stopped then only i should consider System as Down . if any of the A and B are in running status then it should be considered up . is there any way we can do that in qlikview by creating a new column and show as DOWN if both are stopped and show as UP if any one is running in load script

Re: Plz help with grouping data based on column

Anonymous — Tue, 21 Jul 2015 14:54:02 GMT

Hello,

At what interval they should be assumed as running at the same time? Within 1 second, 2 secs, 5 secs?

I can see they have different timestamps.

Serhan

Re: Plz help with grouping data based on column

sgrice — Tue, 21 Jul 2015 15:09:49 GMT

In LOAD SCRIPT:-

statusTable:

load

[CHECKED AT], [System NAME], NAME, STATUS

from SampleFile.Txt(TXT);

left Join (statusTable)

Load

System NAME,

Max(if(STATUS='Running',1,0)) as UP_STATUS

resident statusTable;

Or IN a Chart:-

expression:- Max(if(STATUS='Running',1,0))

in a chart where [System NAME] is a dimension

Re: Plz help with grouping data based on column

Wed, 22 Jul 2015 18:13:47 GMT

Getting Invalid expression error at the line . Please suggest what is wrong here

max(if(Lower([STATUS]) ='running',1,0)) as UP_STATUS

Re: Plz help with grouping data based on column

sgrice — Thu, 23 Jul 2015 07:15:04 GMT

left Join (statusTable)

Load

[System NAME],

Max(if(STATUS='Running',1,0)) as UP_STATUS

resident statusTable

group by [System NAME];

Re: Plz help with grouping data based on column

Thu, 23 Jul 2015 07:26:17 GMT

Thanks it worked

Re: Plz help with grouping data based on column

sgrice — Thu, 23 Jul 2015 07:31:43 GMT

Is this now complete if so can you mark has correct.