topic Isnull in Set Analysis in QlikView

Isnull in Set Analysis

Anonymous — Wed, 11 Jan 2012 07:03:02 GMT

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

Isnull in Set Analysis

Wed, 11 Jan 2012 07:08:27 GMT

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

Isnull in Set Analysis

Anonymous — Wed, 11 Jan 2012 07:52:13 GMT

HI Have tried that but its not wrkng..

Isnull in Set Analysis

vijay_iitkgp — Wed, 11 Jan 2012 08:06:40 GMT

Hi Ankit,

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

Vijay

Isnull in Set Analysis

qliksus — Wed, 11 Jan 2012 10:39:10 GMT

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)

Isnull in Set Analysis

SunilChauhan — Wed, 11 Jan 2012 13:04:28 GMT

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

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

hope this helps

Isnull in Set Analysis

nstefaniuk — Wed, 11 Jan 2012 13:27:09 GMT

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)