Skip to Content
0
Jul 27, 2022 at 08:17 PM

How to add measures within a CASE Statement in IDT?

103 Views

Below is the Case statement that I am trying to implement in IDT. What is the best approach?

Can we create seperate measure fields for Min , Sum and then create CASE ?? Would that work? Please help..

case

when min(VHCL_RPR_ORDR_1ST_DT) is null then 'Zero Visits'

when sum(rpr_ordr_10_mnth_cn)= 0 then 'Zero Visits'

when sum(rpr_ordr_10_mnth_cn)= 0 and sum(rpr_ordr_12_mnth_cn)>0 then 'At Risk'

when sum(rpr_ordr_10_mnth_cn)> 0 then 'Safe'

else 'N/A' end as RISK_CLSFTN_CD ,

case

when sum(rpr_ordr_12_mnth_cn)>1 then 'Loyal'

when sum(rpr_ordr_12_mnth_cn)=0 and min(VHCL_RPR_ORDR_1ST_DT) is not null then 'ReCapture'

when sum(rpr_ordr_12_mnth_cn)=0 and min(VHCL_RPR_ORDR_1ST_DT) is null then 'Capture'

when sum(rpr_ordr_12_mnth_cn)=1 then 'Retained'

else 'N/A' end as CSTMR_CLSFTN_CD