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

[bouderbc]

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

[Anil_Babu_Samineni]

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;

[bouderbc]

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

[sunny_talwar]

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

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

[bouderbc]

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

[sunny_talwar]

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

[bouderbc]

Sunny,

I have the two cases Overall count and By dimension

exemple :

Column: Data1

Value: Toto

expression: count('Toto')

Dimension: Clienta

Thanx a lot

[bouderbc]

Ok Sunny  show me for the Overall count ...

[sunny_talwar]

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;

[bouderbc]

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,

     Login,

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