Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to take count of a field but excluding the nulls ,
how to do that in set analysis expression?
If you start yourself in google, I am sure u will get lot stuff related the same. Anyone of these?
Sum({<field -= {' '}>} field)
Sum({<field = {"<> Null()"}>} field)
Sum({<field -= {'=Len(field)=0'}>} field)
Sum({<field -= {'*'-' '}>} field)
thanks anil,
when I do the nullcount of the field I get null numbers but when am doing like above
Sum({<field -= {' '}>} field)
am getting the same numbers when am writing simply :
count(field)
how is it possible?
Actually set expressions usually exclude nulls.So to exclude nulls from Field1:
Count({<Field1 = {'*'}>} ....)
Will count all non-null values.
Can you read this http://www.qlikviewaddict.com/2012/04/handling-nulls-in-qlikview.html
If not working any thing, Please provide inline data and expected result using Count aggregation.
First Handel the NULL values and find out the nulls and then count them.
Read this post please
so what am trying to do is:
//CASE WHEN ( datefield IS NOT NULL ) THEN 1
// ELSE 0
// END
// AS newfield
above in qlik
May be this?
If(Not IsNull(datefield), 1,0) as newfield
You can do this in script by creating some field for null like-
if(isnull(field),'Null',field) as field
and
write
Sum({<field -= {'Null'}>} field2)
if I need to take count of two fields in my expression,
something like below:
CASE WHEN ( ID IS NOT NULL AND DaTe IS NOT NULL ) THEN 1
ELSE 0
END
AS newfield
above in qlik