topic Re: Null inclusion in QlikView

Null inclusion

priyarane — Wed, 02 Mar 2016 08:44:24 GMT

Hi Team,

How to include null for the below expression

SUM({<%Month = {'Jan'},%Year = {'2016'},CURRENCY = {'$(=$(vCurr))'},INVOLVED_PARTY_NAME =P({<%Month = {'Dec'}, %Year = {'2015'},INVOLVED_PARTY_NAME ={''}>}INVOLVED_PARTY_NAME)>} EAD)

I want the null value for 'INVOLVED_PARTY_NAME'

- Thanks

Re: Null inclusion

Chanty4u — Wed, 02 Mar 2016 08:46:32 GMT

SUM(IsNull({<%Month = {'Jan'},%Year = {'2016'},CURRENCY = {'$(=$(vCurr))'},INVOLVED_PARTY_NAME =P({<%Month = {'Dec'}, %Year = {'2015'},INVOLVED_PARTY_NAME ={''}>}INVOLVED_PARTY_NAME))>} EAD)

Re: Null inclusion

priyarane — Wed, 02 Mar 2016 08:54:50 GMT

Hi Chanty,

it is wrong

Re: Null inclusion

Chanty4u — Wed, 02 Mar 2016 08:58:08 GMT

try dis

SUM({<%Month = {'Jan'},%Year = {'2016'},CURRENCY = {'$(=$(vCurr))'},INVOLVED_PARTY_NAME =P({$<%Month = {'Dec'}, %Year = {'2015'},INVOLVED_PARTY_NAME ={"*"}>}INVOLVED_PARTY_NAME)>} EAD)

Re: Null inclusion

tresB — Wed, 02 Mar 2016 09:01:20 GMT

Try like:

SUM({<%Month = {'Jan'},%Year = {'2016'},CURRENCY = {'$(=$(vCurr))'},INVOLVED_PARTY_NAME =P({<%Month = {'Dec'}, %Year = {'2015'}>+($-<INVOLVED_PARTY_NAME ={'*'}>)}INVOLVED_PARTY_NAME)>} EAD)

Re: Null inclusion

sunny_talwar — Wed, 02 Mar 2016 09:14:05 GMT

May be this:

Sum({<%Month = {'Jan'},%Year = {'2016'}, CURRENCY = {'$(=$(vCurr))'}, INVOLVED_PARTY_NAME = P({<%Month = {'Dec'}, %Year = {'2015'}, INVOLVED_PARTY_NAME ={''}>}INVOLVED_PARTY_NAME) + {"=Len(Trim(INVOLVED_PARTY_NAME)) = 0"}>} EAD)

Re: Null inclusion

priyarane — Wed, 02 Mar 2016 09:17:39 GMT

Hi Tresesco. my result is same as before...

if this is not possible then how to show null values total for INVOLVED_PARTY_NAME

Re: Null inclusion

tresB — Wed, 02 Mar 2016 09:27:40 GMT

You can show null related values (real nulls - hope you understand), using the set expression I showed above. I.e. -

Sum({$-<INVOLVED_PARTY_NAME ={'*'}>)EAD) would return data where your INVOLVED_PARTY_NAME is really null. If you have some non-null values but some spaces or special characters appear as null, you might use expression as Sunny suggested below, however, that would not include the real nulls.

Re: Null inclusion

engishfaque — Wed, 02 Mar 2016 09:35:23 GMT

Dear Priya,

Try this one,

//include

INVOLVED_PARTY_NAME ={'Null()'}

//exclude

INVOLVED_PARTY_NAME -={'Null()'}

Kind regards,

Ishfaque Ahmed

Re: Null inclusion

tyagishaila — Wed, 02 Mar 2016 11:27:03 GMT

is INVOLVED_PARTY_NAME has NULL values or empty ?

try this,

INVOLVED_PARTY_NAME ={ }

Re: Null inclusion

priyarane — Mon, 07 Mar 2016 10:38:44 GMT

Hi Community,

Is ther any other solution as I have tried all the suggeste onces as above and still not getting my null value in my expression.

Ex:

(Sum({<%Month = {'Dec'},%Year = {'2015'}, INVOLVED_PARTY_NAME = P({<%Month = {'Sep'}, %Year = {'2015'}>}INVOLVED_PARTY_NAME)>}EAD)

Sum({<%Month = {'Sep'},%Year = {'2015'}, INVOLVED_PARTY_NAME = P({<%Month = {'Dec'}, %Year = {'2015'}>}INVOLVED_PARTY_NAME)>}EAD))

If I tried the expression for Null as seperate expression and I am getting value for null

Ex for Null:

if(len(INVOLVED_PARTY_NAME)=0 , (sum({<%Month = {'Jan'},%Year = {'2016'}>}EAD)) - sum({<%Month = {'Dec'},%Year = {'2015'}>}EAD))

If I add both like below and I am gettin only null value

(Sum({<%Month = {'Dec'},%Year = {'2015'}, INVOLVED_PARTY_NAME = P({<%Month = {'Sep'}, %Year = {'2015'}>}INVOLVED_PARTY_NAME)>}EAD)

Sum({<%Month = {'Sep'},%Year = {'2015'}, INVOLVED_PARTY_NAME = P({<%Month = {'Dec'}, %Year = {'2015'}>}INVOLVED_PARTY_NAME)>}EAD))

if(len(INVOLVED_PARTY_NAME)=0 , (sum({<%Month = {'Jan'},%Year = {'2016'}>}EAD)) - sum({<%Month = {'Dec'},%Year = {'2015'}>}EAD))

How to total inlude null value.

Pls some suggest.

Re: Null inclusion

tresB — Mon, 07 Mar 2016 10:47:51 GMT

Another way - create a representative sample qvw and share here and let us work on that.

Re: Null inclusion

Digvijay_Singh — Mon, 07 Mar 2016 11:01:41 GMT

try replacing Null with some value in the script and then use this new value in expression to verify your data-

NullAsValue Field1, Field2;

set NullValue='<NULL>';