topic Re: Need Help with Grouping in QlikView

Need Help with Grouping

Pulkit_Thukral — Mon, 31 Aug 2015 07:38:22 GMT

Hi All,

I am getting the data in below fomat for few servers every 10 mins in csv format.

CHECKED AT, SERVER NAME, SERVICE NAME, STATUS

8/6/2015 13:00,Server1,Service A,Running

8/6/2015 13:00,Server1,Service B,Running

8/6/2015 13:00,Server1,Service C,Running

8/6/2015 13:00,Server1,Service D,Running

8/6/2015 13:00,Server1,Service E,Running

8/6/2015 13:00,Server2,Service A,Stopped

8/6/2015 13:00,Server2,Service B,Stopped

8/6/2015 13:00,Server2,Service C,Aborted

8/6/2015 13:00,Server2,Service D,Running

8/6/2015 13:00,Server2,Service E,Running

From the above table if the Service Name "Service C" is not running then we should consider all the other Services of Server to be not running so basically we want to create a new column in the load script whose value should be 0 if for all service for particular server if the "Service C" status is not running.

Note The timestamp values of each service may vary

CHECKED AT, SERVER NAME, SERVICE NAME, STATUS,NEW COLUMN

8/6/2015 13:00,Server1,Service A,Running,1

8/6/2015 13:00,Server1,Service B,Running,1

8/6/2015 13:00,Server1,Service C,Running,1

8/6/2015 13:00,Server1,Service D,Running,1

8/6/2015 13:00,Server1,Service E,Running,1

8/6/2015 13:00,Server2,Service A,Stopped,0

8/6/2015 13:00,Server2,Service B,Stopped,0

8/6/2015 13:00,Server2,Service C,Aborted,0

8/6/2015 13:00,Server2,Service D,Running,0

8/6/2015 13:00,Server2,Service E,Running,0

Please find attached the Sample csv files as well.

Re: Need Help with Grouping

maxgro — Mon, 31 Aug 2015 07:49:41 GMT

LOAD [CHECKED AT],

[SERVER NAME],

[SERVICE NAME],

STATUS

FROM

Sample1.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where len(trim([CHECKED AT]))>0;

left join (s)

load

[SERVER NAME],

STATUS as NewColumn // if you want 0, replace with 0 as NewColumn

Resident s

Where

[SERVICE NAME] = 'Service C'

and STATUS <> 'Running';

Re: Need Help with Grouping

Pulkit_Thukral — Mon, 31 Aug 2015 07:52:05 GMT

Will this left join work if the no of records are more that 1 million

Re: Need Help with Grouping

maxgro — Mon, 31 Aug 2015 08:00:49 GMT

left join will work

maybe you have to add the field [CHECKED AT] and a group by if you have the same server at different time

Re: Need Help with Grouping

maxgro — Mon, 31 Aug 2015 08:04:36 GMT

left join (s)

load

[SERVER NAME],

[CHECKED AT],

max(0) as NewColumn

Resident s

Where

[SERVICE NAME] = 'Service C'

and STATUS <> 'Running'

Group By

[SERVER NAME],

[CHECKED AT];

Re: Need Help with Grouping

Pulkit_Thukral — Mon, 31 Aug 2015 08:05:50 GMT

Could you please attach the qvw file

Re: Need Help with Grouping

maxgro — Mon, 31 Aug 2015 08:08:03 GMT

yes

and the modified .csv I used to test