topic Re: Expression in QlikView

Expression

priyarane — Fri, 04 Dec 2015 15:14:50 GMT

Hi Community,

How to write expression for below one.

DECODE(SUM(case when bis_entity_type_new in ('INDIV', 'SME_RETAIL', 'SME_EU_RET') and asset_class_new not in ('CLAIM_OTH_100') then 0 else ead end), 0, 0,SUM(case when bis_entity_type_new in ('INDIV', 'SME_RETAIL', 'SME_EU_RET') and asset_class_new not in ('CLAIM_OTH_100') then 0 else ead*k_maturity*365.25 end)/SUM(case when bis_entity_type_new in ('INDIV', 'SME_RETAIL', 'SME_EU_RET') and asset_class_new not in ('CLAIM_OTH_100') then 0 else ead end))

Re: Expression

yadav_anil782 — Sat, 05 Dec 2015 06:46:56 GMT

hi,

this is more complex so plz try to share in easy way so we can get your query

Re: Expression

priyarane — Sat, 05 Dec 2015 08:18:40 GMT

But I got it from my users so nothing else I have...

Re: Expression

maxgro — Sat, 05 Dec 2015 08:57:42 GMT

DECODE(

SUM( case when bis_entity_type_new in ('INDIV', 'SME_RETAIL', 'SME_EU_RET')

    and asset_class_new not in ('CLAIM_OTH_100')

then 0

else ead

end

),

0, 0,

SUM( case when bis_entity_type_new in ('INDIV', 'SME_RETAIL', 'SME_EU_RET')

    and asset_class_new not in ('CLAIM_OTH_100')

then 0

else ead*k_maturity*365.25

end

)

/

SUM( case when bis_entity_type_new in ('INDIV', 'SME_RETAIL', 'SME_EU_RET')

    and asset_class_new not in ('CLAIM_OTH_100')

then 0

else ead end

)

)

I try

IF(

SUM( if(match(bis_entity_type_new, 'INDIV', 'SME_RETAIL', 'SME_EU_RET')

and not match(asset_class_new, 'CLAIM_OTH_100')

, 0

, ead

) =0,

/* if sum() =0 --> 0*/

/* else sum() / sum() */

SUM( if(match(bis_entity_type_new, 'INDIV', 'SME_RETAIL', 'SME_EU_RET')

and not match(asset_class_new, 'CLAIM_OTH_100')

, 0

, ead*k_maturity*365.25

)

SUM( if(match(bis_entity_type_new, 'INDIV', 'SME_RETAIL', 'SME_EU_RET')

and not match(asset_class_new, 'CLAIM_OTH_100')

, 0

, ead

)

Re: Expression

Gysbert_Wassenaar — Sat, 05 Dec 2015 09:08:12 GMT

Something like this:

alt(sum(ead*k_maturity*365.25*if(match(bis_entity_type_new,'INDIV', 'SME_RETAIL', 'SME_EU_RET') and not match(asset_class_new'CLAIM_OTH_100'),0,1))/sum(if(match(bis_entity_type_new,'INDIV', 'SME_RETAIL', 'SME_EU_RET') and not match(asset_class_new'CLAIM_OTH_100'),ead)),0)

Re: Expression

priyarane — Sat, 05 Dec 2015 14:33:29 GMT

let me try