Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

null value expression help

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,

AM

1 Solution

Accepted Solutions

null value expression help

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

4 Replies
Highlighted

null value expression help

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

Not applicable

null value expression help

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,

AM

null value expression help

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

Not applicable

null value expression help

Hi Kaushik,

It worked like a charm.

Thanks,

AM