topic Re: How to write expression in script .?Need assistance . in QlikView

How to write expression in script .?Need assistance .

suvechha_b — Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

How to write the below expression in Qlikview script .

1.Count(DISTINCT if(dealstate_id=8 or dealstate_id=9 or dealstate_id=33 or dealstate_id = 34 or dealstate_id = 35,deal_id))

2.Count(DISTINCT if(dealstate_id=33 and LatestForPodium='1',deal_id))

3.sum(if(dealstate_id=33 and LatestForPodium='1',purchasePrice))

For eg , the script in the qlikview model is -

"Deal":

Load

deal_id,

dealgroup_id,

dealstate_id,

batch_ref,

date_updated,

date_created,

dealtype_id

(qvd);

"LatestForPodium":

NoConcatenate

Load

deal_id,

date_created

Resident "Deal"

Where dealstate_id='33' or dealstate_id='34' or dealstate_id='35';

Thanks ,

Re: How to write expression in script .?Need assistance .

YoussefBelloum — Tue, 06 Mar 2018 09:07:27 GMT

Hi,

just to know before trying to help you, why you want to make these calculations on the script ?

Re: How to write expression in script .?Need assistance .

suvechha_b — Tue, 06 Mar 2018 09:13:43 GMT

I am trying to optimize the qlikview model . The report is taking time to open , so I need to convert every expression to script level.

Re: How to write expression in script .?Need assistance .

YoussefBelloum — Tue, 06 Mar 2018 09:21:19 GMT

Ok I see, you can optimise these expression by using set analysis instead of IF.

or you don't want to give a try ?

Re: How to write expression in script .?Need assistance .

suvechha_b — Tue, 06 Mar 2018 09:32:51 GMT

there is a demand to provide it on qlikview script instead of set analysis as well.

Re: How to write expression in script .?Need assistance .

jyothish8807 — Tue, 06 Mar 2018 09:55:23 GMT

Hi Suvecha,

Try like this:

"Deal":

Load

deal_id,

dealgroup_id,

dealstate_id,

batch_ref,

date_updated,

date_created,

dealtype_id

(qvd);

Noconcatenate:

NewTable:

Load

deal_id,

dealgroup_id,

dealstate_id,

batch_ref,

date_updated,

date_created,

dealtype_id,

Count(DISTINCT if(dealstate_id=8 or dealstate_id=9 or dealstate_id=33 or dealstate_id = 34 or dealstate_id = 35,deal_id)) as Newfield1,

Count(DISTINCT if(dealstate_id=33 and LatestForPodium='1',deal_id)) as Newfield2,

sum(if(dealstate_id=33 and LatestForPodium='1',purchasePrice)) as newfield3

resident Deal

group by

deal_id,

dealgroup_id,

dealstate_id,

batch_ref,

date_updated,

date_created,

dealtype_id;

Drop Table Deal;

Note: The field LatestForPodium and purchasePrice should be part of your table which is not present right now,

Br,

Re: How to write expression in script .?Need assistance .

sasiparupudi1 — Tue, 06 Mar 2018 10:26:00 GMT

Soemthing like this..

LatestForPodium:

NoConcatenate

Load

deal_id,

date_created,

IF(Match(dealstate_id),33,34,35),1,0) as flagdeal_id,

IF(dealstate_id=33 and LatestForPodium='1',1,0) as flagLastPodium,

Resident Deal;

Left Join(LatestForPodium)

Load Distinct

deal_id,

Sum(flagdeal_id) as CountOfDeals,

Sum(flagLastPodium) as CountOfLastPodiums

Sum(if(flagLastPodium=1,purchasePrice)) as TotalpurchasePrice

Resident LatestForPodium

Group by deal_id;