Is it possible to create a new field which sums the values of other fields during Load

[nigelapt]

Hi Folks

As always, I really appreciate your help and support in advance.

I am loading in a server inventory as follows:

DEVICENAME, SERVICE1_Installed, SERVICE2_Installed, SERVICE3 Installed

server.xyz.com, yes, no, yes

I want to have a column which gives a total where the service installed is Yes in the 3 services listed:

DEVICENAME, SERVICE1_Installed, SERVICE2_Installed, SERVICE3 Installed,TotalServices

server.xyz.com, yes, no, yes,2

Is this possible to do as the script loads?

Thanks

Nigel

[mruehl]

I'd try

substringcount(SERVICE1_Installed&SERVICE2_Installed&SERVICE3_Installed) as TotalServices

[parevrac]

Nigel

you could try adding this to the data load script

left join (yourtablename)
load DEVICENAME,
sum(service1) + sum(service1) + sum(service1) as TotalServices
group by DEVICENAME;
load DEVICENAME,
if(SERVICE1_Installed='yes',1, 0) as service1,
if(SERVICE2_Installed='yes',1, 0) as service2,
if(SERVICE3_Installed='yes',1, 0) as service3,
resident yourtablename;

[Raja2022]

Hi, Give a try with the below code and you will get your final output:

Server_Inventory:

Load
*
,SERVICE1 + SERVICE2 +SERVICE3 as TotalServices
;

Load
DEVICENAME
,SERVICE1_Installed
,SERVICE2_Installed
,SERVICE3_Installed
,If(SERVICE1_Installed='yes',1,0) as SERVICE1
,If(SERVICE2_Installed='yes',1,0) as SERVICE2
,If(SERVICE3_Installed='yes',1,0) as SERVICE3
;

Load * inline [
DEVICENAME, SERVICE1_Installed, SERVICE2_Installed, SERVICE3_Installed,TotalServices
server.xyz.com, yes, no, yes
];

Hope this helps.

[Raja2022]

Also you can give a try with this second method as below:

Server_Inventory:

Load
DEVICENAME
,SERVICE1_Installed
,SERVICE2_Installed
,SERVICE3_Installed
,substringcount(SERVICE1_Installed&SERVICE2_Installed&SERVICE3_Installed,'yes') as TotalServices
;

Load * inline [
DEVICENAME, SERVICE1_Installed, SERVICE2_Installed, SERVICE3_Installed
server.xyz.com, yes, no, yes
];

[nigelapt · Accepted solution]

Hi Mruehl

That set me on the right track.  This worked for me:

substringcount([Disable Service1],'Yes') + substringcount([Downgrade Service2],'Yes') + substringcount([Disable Service3],'Yes') + substringcount([Disable Service4],'Yes') + substringcount([Disable Service5],'Yes')+ substringcount([Disable Service6],'Yes') as [OS MON Deviations],