Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
Hi Kaushik,
It worked like a charm.
Thanks,
AM