Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
suvechha_b
Creator III
Creator III

How to write expression in script .?Need assistance .

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 ,



6 Replies
YoussefBelloum
Champion
Champion

Hi,

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

suvechha_b
Creator III
Creator III
Author

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
Champion
Champion

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
Creator III
Creator III
Author

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

jyothish8807
Master II
Master II

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

Best Regards,
KC
sasiparupudi1
Master III
Master III

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;