topic Re: Is it possible to create a new field which sums the values of other fields during Load in QlikView

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

nigelapt — Fri, 18 Feb 2022 10:38:41 GMT

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

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

ManuelRühl — Fri, 18 Feb 2022 11:54:51 GMT

I'd try

substringcount(SERVICE1_Installed&SERVICE2_Installed&SERVICE3_Installed) as TotalServices

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

parevrac — Fri, 18 Feb 2022 11:55:13 GMT

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;

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

Raja2022 — Sat, 19 Feb 2022 13:26:53 GMT

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.

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

Raja2022 — Sat, 19 Feb 2022 13:34:38 GMT

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

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

nigelapt — Wed, 23 Feb 2022 12:33:15 GMT

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