topic Re: Avoiding Null in Text Object in QlikView

Avoiding Null in Text Object

Anonymous — Wed, 10 Jun 2015 09:17:37 GMT

Hi all,

How we can avoid Null() values in set analysis expressions.

We have a requirement, where we want distinct count of dim1, where dim2 should not be Null.

We have tried,

- dim2={*}-{''}

- dim2 = {'*?'}

- Not IsNull(dim2)

all these expressions are not working in below case

count(distinct {<Expressions>}dim1)

Please assist.

Regards,

Re: Avoiding Null in Text Object

sunny_talwar — Wed, 10 Jun 2015 09:19:03 GMT

you can try this:

{<dim2 = {'*?'}>}

Update: Just realized that you mentioned that the above is not working. You can try this

{<dim2 = {"=Len(Trim(dim2)) <> 0"}>}

Re: Avoiding Null in Text Object

Anonymous — Wed, 10 Jun 2015 09:20:59 GMT

already tried, not working.

Re: Avoiding Null in Text Object

Wed, 10 Jun 2015 09:21:03 GMT

Hi,

Please try this code:

=Count(distinct{<dim1={'<>'''}>}dim1)

Regards,

Jemimah

Re: Avoiding Null in Text Object

sunny_talwar — Wed, 10 Jun 2015 09:21:26 GMT

try this:

{<dim2 = {"=Len(Trim(dim2)) <> 0"}>}

Re: Avoiding Null in Text Object

Anonymous — Wed, 10 Jun 2015 09:30:26 GMT

not working....:(

Re: Avoiding Null in Text Object

Anonymous — Wed, 10 Jun 2015 09:30:34 GMT

nope

Re: Avoiding Null in Text Object

sunny_talwar — Wed, 10 Jun 2015 09:33:32 GMT

Would you be able to share a sample which shows the issue you are having?

Re: Avoiding Null in Text Object

Anonymous — Wed, 10 Jun 2015 09:46:57 GMT

If i try it on altogether new app, with dummy data, then its working as expected, but with complex data model (which i cant share) its giving problem

Re: Avoiding Null in Text Object

antoniotiman — Wed, 10 Jun 2015 09:52:42 GMT

How are You loading Table with dim1 and dim2 ? From Load or DB ?

Re: Avoiding Null in Text Object

sunny_talwar — Wed, 10 Jun 2015 09:59:31 GMT

Create a table with dim1 and dim2 as dimensions and use this as expression =Len(Trim(dim2) and check if you are getting any 0's in there

Re: Avoiding Null in Text Object

Wed, 10 Jun 2015 10:17:47 GMT

Hi MK,

count(distinct {<dim2={"*"}>}dim1)

will select all values from dim2 and as such will not include an true null values from that field.

If this does not work then it means that rather than true null values in dim2, you potentially have zero length strings instead which you are calling nulls.

For something like that the below should work

count(distinct {<dim2={"=Len(dim2)>0"}>}dim1)

but what I would suggest first is to identify what 'nulls' you have, to know how best to deal with them, maybe have a look here

Finding NULL

NULL – The Invisible Nothing

hope that helps

Joe