topic Re: Need help in set analysis and null handling in expression in App Development

Need help in set analysis and null handling in expression

ashmitp869 — Mon, 20 Feb 2023 22:01:30 GMT

Hi there,

I want to calculate the measures Uncoded Service Events show as 1 i.e sum(SE) where [Coded Flag] is null.

here are my data :

I have used this query but the expression is not working.

Sum({$<[Coded Flag] ={'Uncoded'}> + <[Coded Flag]={"=isnull([Coded Flag])"}>}[SE])

Re: Need help in set analysis and null handling in expression

BrunPierre — Mon, 20 Feb 2023 23:37:39 GMT

[Coded Flag]={"=Len(Trim([Coded Flag]))=0"}

Re: Need help in set analysis and null handling in expression

ashmitp869 — Mon, 20 Feb 2023 23:11:13 GMT

Hi there ,

the expression is still not working.

even though the

Re: Need help in set analysis and null handling in expression

ashmitp869 — Mon, 20 Feb 2023 23:17:19 GMT

Actually , [Coded Flag] value is "Coded" and "Uncoded"

thus or part is not working- I need to check [Coded Flag] is null

Sum({$<[Coded Flag] ={'Uncoded'}> + <[Coded Flag]={"=Len(Trim([Coded Flag]))=0"}>}[SE])

Re: Need help in set analysis and null handling in expression

BrunPierre — Mon, 20 Feb 2023 23:46:46 GMT

Suppress null values on the dimensions.

Re: Need help in set analysis and null handling in expression

ashmitp869 — Mon, 20 Feb 2023 23:51:43 GMT

The coded Flag is not on the dim, but for testing I brought it.

Is main field is DRG Code - unable to suppress the DRG Code. As I need to show the DRG Code if null.

Is there any other way to handle it - with out suppressing the DRG Code.?

Re: Need help in set analysis and null handling in expression

BrunPierre — Tue, 21 Feb 2023 00:54:52 GMT

To test if the filter actually works, disable all columns except SE.

Re: Need help in set analysis and null handling in expression

ashmitp869 — Tue, 21 Feb 2023 01:13:25 GMT

I tried to handle the null by load script -

[v_DIM_AR_DRG_TMP]:

seleclt

,CASE WHEN [DRG Code]= '960Z' OR [DRG Code] IS NULL OR len(trim( [DRG Code]))=0 THEN 1 ELSE 0
END AS CFlag

from table ;

[v_DIM_AR_DRG]:
LEFT KEEP([v_FACT_AP_SE_WAU])
LOAD
[DRG Code]

,if(CFlag=0,'Coded','Uncoded') as [Coded Flag]
, if(len(trim( [DRG Code]))=0,1,0) as Null_Flag

Resident v_DIM_AR_DRG_TMP;

but still can't handle it

Re: Need help in set analysis and null handling in expression

BrunPierre — Tue, 21 Feb 2023 07:29:48 GMT

If row one is all you need, these two expressions will do.

Count({$<[Coded Flag] ={'Uncoded'}>}[# Rows])

Sum({$<[Coded Flag]={'Uncoded'}>}[SE])