Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ,
Hi,
just to know before trying to help you, why you want to make these calculations on the script ?
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.
Ok I see, you can optimise these expression by using set analysis instead of IF.
or you don't want to give a try ?
there is a demand to provide it on qlikview script instead of set analysis as well.
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
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;