topic Re: null value in QlikView

null value

Anonymous — Tue, 09 Feb 2016 08:17:24 GMT

Hello everyone

I wrote these three commands but it don't work correctly

First line count M_compositionID

Second line count M_compositionID that are not null

Third line count M_compositionID that are null

I will appreciate if any one can help me.

Re: null value

Chanty4u — Tue, 09 Feb 2016 09:50:14 GMT

if use in stright table expresion tab right side dow u hve option total mode as null count

Re: null value

sunny_talwar — Tue, 09 Feb 2016 09:51:34 GMT

I am guessing that only the last one doesn't work, Try this if the other two are working (a different location for the negative sign)

Num(Count({<P_NationalCode -= {'*'}>} M_CompositionID), '#,##0')

Re: null value

tamilarasu — Tue, 09 Feb 2016 09:53:21 GMT

Use like below

Num(Count({<P_NationalCode-={'*'}>}M_CompostionID>}),'#,##0')

Re: null value

Chanty4u — Tue, 09 Feb 2016 09:54:18 GMT

num(count({$<P_Nationalcode = {'*'}>} M_compID)),'#.##0')

count({$<P_Nationalcode ={"=isnull(M_compID)"}>} P_Nationalcode )

Re: null value

kashaboinaraj — Tue, 09 Feb 2016 10:35:15 GMT

try this

Num(count({<p_Nationalcode={'*'}>}M_CompositionID),'#,##0')

Re: null value

jagan — Wed, 10 Feb 2016 03:37:15 GMT

Hi,

You have to use -={'*'} for getting the null values

Num(Count({<P_NationalCode -= {'*'}>} M_CompositionID), '#,##0')

For Non Null values

Num(Count({<P_NationalCode = {'*'}>} M_CompositionID), '#,##0')

Hope this helps you.

Regards,

jagan.

Re: null value

HirisH_V7 — Wed, 10 Feb 2016 04:17:27 GMT

Hi,

Check this three expressions,

Count
=Num(Count(M_CompositionID),'#,##0')

Not Null
=num(count({1-<P_Nationalcode -= {'*'}>} M_CompositionID),'#.##0')

Null
=num(count({1-<P_Nationalcode = {'*'}>} M_CompositionID),'#.##0')

Hope this Helps,

Hirish

Re: null value

jonathandienst — Wed, 10 Feb 2016 04:52:42 GMT

Let me state firmly that the structure P_Nationalcode -= {'*'} does NOT select null values. Set analysis works like selections, and just as you cannot select null values in a list box, so a simple filter in set analysis cannot select nulls.

Mys suggestions are

(1) use Count(If(IsNull(P_Nationalcode), M_CompositionID))

This will work correctly, but may perform poorly if your data set is large

(2) create a flag as a derived field in the load and use the flag in your set analysis:

LOAD ...

P_Nationalcode,

M_CompositionID

If(IsNull(P_Nationalcode), 1, 0) As NullCode,

...

Then use Count({<NullCode = {1}>} M_CompositionID)

Re: null value

HirisH_V7 — Wed, 10 Feb 2016 05:10:21 GMT

Hi,

In back end script,

If(IsNull(P_Nationalcode), 1,  P_Nationalcode) As P_Nationalcode,

Hope this will be helpful,

Hirish

Re: null value

jonathandienst — Wed, 10 Feb 2016 05:14:55 GMT

Well as long as none of the real P_Nationalcodes are = 1 !

That's why I recommend a separate flag field to avoid confusing your users and possibly corrupting your data.

Re: null value

Anonymous — Sun, 14 Feb 2016 08:52:15 GMT

thank you for your guidance

Re: null value

Anonymous — Sun, 14 Feb 2016 08:52:29 GMT

thank you for your guidance

Re: null value

Anonymous — Sun, 14 Feb 2016 08:52:36 GMT

thank you for your guidance

Re: null value

Anonymous — Sun, 14 Feb 2016 08:52:45 GMT

thank you for your guidance

Re: null value

Anonymous — Sun, 14 Feb 2016 08:52:53 GMT

thank you for your guidance