topic Re: How to generate a calculated field with count ( ) or Sum in the load script ? in QlikView

How to generate a calculated field with count ( ) or Sum in the load script ?

bouderbc — Mon, 26 Jan 2026 18:19:17 GMT

Hi experts !

I want to generate a calculated column in the load script to use it in my set analysis

Case :

I want to load this expression as a column

Expression:

=Count({<[State] = {'UK'}>} [Customer])

Example:

=Count({<[State] = {'UK'}>} [Customer]) as UK_CUSTOMERS

Please tell me how can i do that ?

Regards

Ali

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

Anil_Babu_Samineni — Tue, 17 Oct 2017 16:00:50 GMT

One solution could be

Fact:

Load State, Customer from Table;

Right Join (Fact)

Load State, Count(Customer) as UK_CUSTOMERS Resident Fact Where State = 'UK' Group By State;

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

bouderbc — Wed, 18 Oct 2017 08:30:51 GMT

Thanx Anil , I will try it and i'll tell u if it works

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

sunny_talwar — Wed, 18 Oct 2017 11:35:32 GMT

Do you need this count based on another dimension or overall count?

Count(If(State = 'UK', Customer)) as UK_CUSTOMERS

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

bouderbc — Thu, 19 Oct 2017 12:12:56 GMT

Hi Sunny ,

Pleasure to see u

like the expression used in the set analysis , i want to generate a calculated field with the function count :

exemple :

Column: Data1

Value: Toto

expression: count('Toto')

Needs: To generate a calculated field in the load script based on the expression count('Toto')

load

something like count('Toto') as Test

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

sunny_talwar — Thu, 19 Oct 2017 12:16:37 GMT

Are you looking to get the overall count? or count by some dimensions?

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

bouderbc — Thu, 19 Oct 2017 12:26:21 GMT

Sunny,

I have the two cases Overall count and By dimension

exemple :

Column: Data1

Value: Toto

expression: count('Toto')

Dimension: Clienta

Thanx a lot

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

bouderbc — Thu, 19 Oct 2017 12:48:02 GMT

Ok Sunny show me for the Overall count ...

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

sunny_talwar — Thu, 19 Oct 2017 12:50:56 GMT

May be like this (using Anil's code but showing that your Fact table can have more than the two fields and doing a Left Join instead of Right Join)

Fact:

Load State,

Customer,

...

FROM Table;

Left Join (Fact)

Load State,

Count(Customer) as UK_CUSTOMERS

Resident Fact

Where State = 'UK'

Group By State;

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

bouderbc — Thu, 19 Oct 2017 13:37:02 GMT

Please Help !

it doesn't work , but i put the complete official script please Have a look and tell me what should i do for the expression :

=Count({<[EtatBitLockerChiffrement] = {'conforme'}>} [Client])/Count([Client])*100

Needs: put the expression in the load script as a Calculated field

=Count({<[EtatBitLockerChiffrement] = {'conforme'}>} [Client])/Count([Client])*100 as TOTO

Security:

LOAD

Client &'|'&Machine as Key,

OS_Version,

EtatBitLockerChiffrement,

Client,

TypePoste,

ZC_ProductVersion,

ZC_ActiveState,

Age_Def_Antivirus,

Qualif_Age_of_Antivirus,

date(DateLogin) as Date_Login,

month(DateLogin) as Mois_Login,

day(DateLogin) as Jour_Login,

year(DateLogin)as Annee_Login,

month(DateLogin) &'|'& year(DateLogin) as Mois_Année_Login,

Machine,

EtatTPMActivationBIOS,

EtatTPMValidationWindows,

EtatTPMPropriete,

DernierEchecFixBitLocker,

DerniereActionRealisee,

SEP_FWRunningStatus,

SEP_LatestVirusDefsDate,

SEP_LatestVirusDefsRevision,

SEP_DeployRunningVersion,

SEP_LastSuccessfulScanDateTime,

SEP_Smc_engine_status,

SEP_PolicyMode,

ZC_NextActiveState,

ZC_InstallDate,

PathADMachine,

PathADUser

FROM

[..\QVD\QVD_TR\TBD_PDT_SECURITE_SECU_ALL.QVD]

(qvd );

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

Peter_Cammaert — Thu, 19 Oct 2017 14:33:28 GMT

Add this after your LOAD statement:

LEFT JOIN (Security)

LOAD Client,

100.0*Count(IF (EtatBitLockerChiffrement = 'conforme', Client))/Count(Client) AS TOTO

RESIDENT Security

GROUP BY Cliient;

Best,

Peter

Re: How to generate a calculated field with count ( ) or Sum in the load script ?

bouderbc — Fri, 20 Oct 2017 09:20:57 GMT

Awesome , Thanx Peter ! I have another question :

i want to use the field TOTO which is a calculated field with the dimension month in a bar chart and show the last six months ? How can i do that ?