topic Re: urgent requirement in QlikView

urgent requirement

Anonymous — Thu, 07 Dec 2017 05:53:14 GMT

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

how to do that in set analysis expression?

Re: urgent requirement

Anil_Babu_Samineni — Thu, 07 Dec 2017 05:56:48 GMT

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)

Re: urgent requirement

Anonymous — Thu, 07 Dec 2017 06:09:56 GMT

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?

Re: urgent requirement

jonathandienst — Thu, 07 Dec 2017 06:12:48 GMT

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

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

Will count all non-null values.

Re: urgent requirement

Anil_Babu_Samineni — Thu, 07 Dec 2017 06:15:12 GMT

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.

Re: urgent requirement

its_anandrjs — Thu, 07 Dec 2017 06:23:10 GMT

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

Read this post please

NULL handling in QlikView

Re: urgent requirement

Anonymous — Thu, 07 Dec 2017 06:52:32 GMT

so what am trying to do is:

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

above in qlik

Re: urgent requirement

Anil_Babu_Samineni — Thu, 07 Dec 2017 07:11:24 GMT

May be this?

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

Re: urgent requirement

neha_shirsath — Thu, 07 Dec 2017 07:14:31 GMT

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)

Re: urgent requirement

Anonymous — Thu, 07 Dec 2017 07:54:17 GMT

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

Re: urgent requirement

neha_shirsath — Thu, 07 Dec 2017 08:00:26 GMT

May be like this-

Add in your script-

if(not IsNull(ID) and not IsNull(Date),1,0) as newfield

You can use this flag in front end.

Re: urgent requirement

Anonymous — Thu, 07 Dec 2017 08:29:39 GMT

so den in front end I shud use something like ,

count(newfield={"1"})

which will exclude the null values?

Re: urgent requirement

neha_shirsath — Thu, 07 Dec 2017 08:55:33 GMT

No,

if(not IsNull(ID) and not IsNull(Date),1,0) as newfield

Here, 1 means Not null and 0 means null vaues.

So you expression will be-

Count({<newfield={1}>}ID) //Not Considering Null Values

Re: urgent requirement

Anonymous — Fri, 08 Dec 2017 11:59:54 GMT

but I also have a case where I need to take a count of my date field(there is only one field) excluding the null values,

so how to do that in script or set analysis?

Re: urgent requirement

neha_shirsath — Fri, 08 Dec 2017 12:04:57 GMT

Not understood. Count of date field means? or You want to count ID based on Date field?

Re: urgent requirement

Anonymous — Fri, 08 Dec 2017 12:34:47 GMT

no,

its just that I need to take count(date field ) only ,but excluding the null values

Re: urgent requirement

vishsaggi — Fri, 08 Dec 2017 12:43:43 GMT

Create a two flags. One for your ID and one for your date.

May be try this as Neha suggested:

if(not IsNull(ID) ,1,0) as IDCount

if(not IsNull(Date),1,0) as DateCount

Not sure if this is what you are looking for?

Re: urgent requirement

Anonymous — Fri, 08 Dec 2017 13:03:15 GMT

so on front end I need to show the count of this field ,

how to do in set analysis

Re: urgent requirement

Anonymous — Fri, 08 Dec 2017 13:20:31 GMT

=count({<DateCount={'1'}>}DateCount) ,will this do?

Re: urgent requirement

vishsaggi — Fri, 08 Dec 2017 15:21:45 GMT

It should did you check?