topic null value expression help in QlikView

null value expression help

Tue, 01 May 2012 17:49:29 GMT

Hi,

I have a field called Resolved Date, which has null and non-null values. I want to count all cases where Resolved Date is null

My SQL Server expression is

SELECT Count([Case ID])

FROM [DB].[dbo].[Table]

WHERE [Assigned Group] ='Service Desk'

AND (isnull([Resolved Date/Time],'')='')

My QV expression which is not working properly is

=count({<[Assigned Group] = {'Service Desk'} >}

if(IsNull(ResolvedDate)=''), [Case ID]))

I'm unable to properly formulate the null expression?

I know that the values for Resolved Date are not blank strings because when I use Case Id and Resolved Date in a Straight table chart and check Suppress when Null, all the records with Resolved Date as null/empty values get filtered out.

I would highly appreciate any help.

Thanks,

null value expression help

kaushiknsolanki — Tue, 01 May 2012 18:05:21 GMT

Hi,

You can do one change in datamodel itself.

Create a field which will tell you that the Resoved date is null or not.

Something like this

if(isnull(ResolvedDate),1,0) as NullCheck

How you can use this as filter.

Regards,

Kaushik Solanki

null value expression help

Tue, 01 May 2012 18:44:07 GMT

Thanks Kaushik,

I did as you suggested. However, when I pulled in ResolvedDate and Null Check in a table box, I got a 0 value under Null Check field corresponding to a Blank value under Resolved Date.

This makes me wonder if it is not a null but a blank string issue.

What made me think it was a null value issue in the first place was the test that I had done as I mentioned in my initial post.

Can you tell me how I would modify my expression to accomodate for a blank string.

(When researching cases similar to this issue I found the expressions which used

if(len(trim(Expr) = 0)), but was unable to get it working properly.

Thanks,

null value expression help

kaushiknsolanki — Tue, 01 May 2012 18:49:03 GMT

Hi,

First thing create a normal textbox of that field and see if you can see any blank values. if there then copy that and then use expression in load as

if(Resolveddate = ' ',1,0)

Regards,

Kaushik Solanki

null value expression help

Wed, 02 May 2012 02:53:06 GMT

Hi Kaushik,

It worked like a charm.

Thanks,