Need Help with Grouping

[Pulkit_Thukral]

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.

[maxgro]

s:

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';

[Pulkit_Thukral]

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

[maxgro]

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

[maxgro]

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];

[Pulkit_Thukral]

Could you please attach the  qvw file

[maxgro]

yes

and the modified .csv  I used to test