Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

urgent requirement

I need to take count of a field but excluding the nulls ,

how to do that in set analysis expression?

19 Replies
Anil_Babu_Samineni

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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?

jonathandienst
Partner - Champion III
Partner - Champion III

Actually set expressions usually exclude nulls.So to exclude nulls from Field1:

     Count({<Field1 = {'*'}>} ....)

Will count all non-null values.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anil_Babu_Samineni

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.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
its_anandrjs

First Handel the NULL values and find out the nulls and then count them.

Read this post please

NULL handling in QlikView

Anonymous
Not applicable
Author

so what am trying to do is:

//CASE WHEN ( datefield IS NOT NULL ) THEN 1
// ELSE 0
// END
// AS newfield

above in qlik

Anil_Babu_Samineni

May be this?

If(Not IsNull(datefield), 1,0) as newfield

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
neha_shirsath
Specialist
Specialist

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)

Anonymous
Not applicable
Author

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