Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
nigelapt
Contributor III
Contributor III

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

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

 

Labels (1)
1 Solution

Accepted Solutions
nigelapt
Contributor III
Contributor III
Author

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

 

View solution in original post

5 Replies
mruehl
Creator III
Creator III

I'd try

substringcount(SERVICE1_Installed&SERVICE2_Installed&SERVICE3_Installed) as TotalServices

parevrac
Contributor III
Contributor III

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
Contributor III
Contributor III

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

 

Raja2022_0-1645277186774.png

 

Hope this helps.

Raja2022
Contributor III
Contributor III

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

Raja2022_1-1645277615430.png

 

nigelapt
Contributor III
Contributor III
Author

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