How to include nulls and exclude a specific value?

[Anonymous]

Hi,

I have a table that will count the IDs, but I need to exclude some values, the problem is that the field may have nulls or blanks.

I am using something like this:

count({$<[Code] -= {'Test'}>}[Case Count])

When I use something like that, it will also remove from the list the ones that are null. How can I use a similar expression that not exclude nulls?

Please let me know.

[sunny_talwar]

May be do this in the script:

LOAD Code,

           [Case Count],

           If(Code = 'Test', 0, 1) as Flag

FROM ....;

and then this:

Count({<Flag = {1}>}[Case Count])

[Anonymous]

Is there another way to do it, directly on the Expression?

[sunny_talwar]

You can try this:

Count({$<[Code] = e({<Code = {'Test'}>} Code)>}[Case Count])

[Anonymous]

That works but excluded the nulls. I need the nulls in the view.

[sunny_talwar]

Do you have a unique identifier in your table? Lets say you have UniqueKey as unique identifer....

Count({$<UniqueKey = {"=Code <> 'Test' or Len(Trim(Code)) = 0"}>}[Case Count])

[Anonymous]

count({$<UniqueKey = {"=[Code]<>'test' or Len(Tim([Closure Code])) = 0"}>}[Case Count]) 

I am getting errors in expression message.

[sunny_talwar]

Do you have field name UniqueKey in your application? I guess not, it was just a place holder to replace with a field which uniquely identify each row from the table which include Code and Case Count.

Does that make sense?

[Anonymous]

Yes, makes sense. But it's still not working, not showing anything.

Count({$<CaseId= {"=Code <> 'Test' or Len(Trim(Code)) = 0"}>}[Case Count])

[sunny_talwar]

How about just this?

Count({$<CaseId= {"=Code <> 'Test' "}>}[Case Count])