How to write expression in script .?Need assistance .

[suvechha_b]

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 ,

[YoussefBelloum]

Hi,

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

[suvechha_b]

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.

[YoussefBelloum]

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

or you don't want to give a try ?

[suvechha_b]

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

[jyothish8807]

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,

KC

[sasiparupudi1]

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;