Isnull in Set Analysis

[Anonymous]

HI,

Please any one can help me on this.

OLD Expression:

Count(Distinct If(FndCT_SDF_Ctgy = 'Same Day', PRE_AUDIT_ACCT_SCHDL))

/ (Count(Distinct PRE_AUDIT_ACCT_SCHDL) - Count(Distinct If(FND_TAG = 'Funded'

       And IsNull(FndCT_SDF_Ctgy), PRE_AUDIT_ACCT_SCHDL)))

New Expression (Using Set Analysis):

=count( DISTINCT {<[FndCT_SDF_Ctgy] = {'Same Day'} >} PRE_AUDIT_ACCT_SCHDL)

/ (Count(Distinct PRE_AUDIT_ACCT_SCHDL) - count( DISTINCT {<[FND_TAG] = {'Funded'},FndCT_SDF_Ctgy={""}>} PRE_AUDIT_ACCT_SCHDL))

I have made the New Expression using Set Analysis, but the isnull part is not working in it.. Can any one plz help me on this.

Regards,

Ankit

[#2]

Hi,

Try to use simple like this

=count( DISTINCT {<[FndCT_SDF_Ctgy] = {'Same Day'} >} PRE_AUDIT_ACCT_SCHDL)

/ (Count(Distinct PRE_AUDIT_ACCT_SCHDL) - count( DISTINCT {<[FND_TAG] = {'Funded'},FndCT_SDF_Ctgy={}>} PRE_AUDIT_ACCT_SCHDL))

Except that you could also do the same thing at back end where making the isnull flag for the FndCT_SDF_Ctgy.

Then calling that flag in your set analysis.

Regards,

Nitin Jain

[Anonymous]

HI Have tried that but its not wrkng..

[vijay_iitkgp]

Hi Ankit,

There is workaround as Nitin suggested create a flag at backend for Null values and use that flag in set analysis.

Vijay

[qliksus]

hi,

you can use the variable

set NullInterpret=' '; in the script

and use the set analysis as

count({<FndCT_SDF_Ctgy={' '}>} PRE_AUDIT_ACCT_SCHDL)

[SunilChauhan]

count({<FndCT_SDF_Ctgy={0}>} PRE_AUDIT_ACCT_SCHDL)

or

count({<FndCT_SDF_Ctgy={$(=Null())}>} PRE_AUDIT_ACCT_SCHDL)

hope this helps

[nstefaniuk]

Sunil, does it work for you ? I have tried your syntax and it doesn't work for me (SR10).

Syntaxes that work for me:

sum({$-<FndCT_SDF_Ctgy={"*"}>}1)

sum({$<id= {"=isnull(FndCT_SDF_Ctgy)=-1"}>}1)

sum({$<id= {"=Len(FndCT_SDF_Ctgy) = 0"}>}1)

I think that it's not possible to write in set analysis "Field = {empty set}" so you have 3 solutions:

- select all (or all possible values) minus the non null values

- select, through an id of the rows, all the rows for which the value is null

- select, through an id of the rows, all the rows for which the value has a size of 0 (take care with string that can have no size but are not null)