null value

[Anonymous]

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.

[Chanty4u]

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

[sunny_talwar]

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')

[tamilarasu]

Use like below

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

[Chanty4u]

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

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

[kashaboinaraj]

Hi

try this

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

[jagan]

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.

[HirisH_V7]

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

[jonathandienst]

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)

[HirisH_V7 · Accepted solution]

Hi,

In back end script,

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

Hope this will be helpful,

Hirish