topic Re: Expression help needed for Count(Distinct Field1&Field2&Field3) return 0 for null in Fie in QlikView

Expression help needed for Count(Distinct Field1&Field2&Field3) return 0 for null in Field3

mscomens — Thu, 01 Oct 2020 13:34:45 GMT

Hello community,

I am not formally trained in QlikView but find myself learning as I go.

I have an expression for s straight table that looks like this:

Count(Distinct Field1&Field2&Field3)

I would like something where I can do this:

Count(Distinct Field1&Field2&Field3) Where Field3 not null

I am looking to get a 0 when Field1 and Field2 still has a row in the table but Field 3 would be null in that row.

Currently Field3 is not included in the straight table as a dimension because that creates a separate row for each entry that is not null in Field3.

Values in all dimensions are strings

I hope this makes sense

Any help you could provide would be appreciated.

Michael

Re: Expression help needed for Count(Distinct Field1&Field2&Field3) return 0 for null in Fie

sunny_talwar — Thu, 01 Oct 2020 13:37:03 GMT

@mscomens Try this

Count({<Field3 *= {"*"}>}DISTINCT Field1&Field2&Field3)

Re: Expression help needed for Count(Distinct Field1&Field2&Field3) return 0 for null in Fie

mscomens — Thu, 01 Oct 2020 13:52:41 GMT

Thank you Sunny! That was exactly what I needed. Can you help me understand what is happening in that expression or point me where to look at what the syntax is doing.

Re: Expression help needed for Count(Distinct Field1&Field2&Field3) return 0 for null in Fie

sunny_talwar — Thu, 01 Oct 2020 14:50:20 GMT

@mscomens - All this is saying is that include only those rows from the data where there is some data available in Field3. {<Field = {"*"}>}. Added * before = to let selection in Field3 to filter the expression

Re: Expression help needed for Count(Distinct Field1&Field2&Field3) return 0 for null in Fie

mscomens — Thu, 01 Oct 2020 15:01:59 GMT

@sunny_talwar - Thank you for the explanation!