topic Issue with NULL/Blank records in QlikView

Issue with NULL/Blank records

Thu, 03 Jul 2014 11:00:49 GMT

I am encountering a strange problem with NULL/Blanks in my dimension data.

I have multiple records in my data set for each dimension value and I am summing up the mesasures against it. I have sorted the dimension based on sum of 1st measure column.

If you see the screenshot 1, the last record is null and there is no record for ELMIRA,NY even though there is data.

When I change the dataset and replace all nulls with 'BLANK', I get the entire data. Please see screenshot 2

This is affecting the totals as well. Please advise how to rectify this or handle nulls.

Re: Issue with NULL/Blank records

Gysbert_Wassenaar — Thu, 03 Jul 2014 11:06:23 GMT

It's impossible to tell from the screenshots what's going on in your document.

Maybe this document helps: NULL handling in QlikView

Re: Issue with NULL/Blank records

tresB — Thu, 03 Jul 2014 11:07:12 GMT

So what do you want data to be like? Exclude the them ?

Re: Issue with NULL/Blank records

Thu, 03 Jul 2014 11:09:48 GMT

I want to have all the data.

I am ok with a row for blank, but ELMIRA data should also show up. so that the totals match.

Re: Issue with NULL/Blank records

SunilChauhan — Thu, 03 Jul 2014 11:11:04 GMT

I think ELMIRA,NY value not related to null values. or data change at later stage and ELMIRA,NY got introduced..

are you comining first column based on multiple field

Re: Issue with NULL/Blank records

Thu, 03 Jul 2014 11:13:48 GMT

Sunil

ELMIRA is present in both cases. Just that in case 1, it does not come up after the row for Null values.

I have sorted it descending and ELMIRA totals are lower than those against Null.

Re: Issue with NULL/Blank records

tresB — Thu, 03 Jul 2014 11:13:51 GMT

So you mean - when null value is replaced by BLANK it is additionally one extra member - 'ELMIRA' ? Could you create a small sample with sample data that shows the issue?

Re: Issue with NULL/Blank records

SunilChauhan — Thu, 03 Jul 2014 11:19:51 GMT

when you put Blank then it considering like alphabet and sort according to that

if there are only 3-4 values

then you can set sort order using

properties->sort -> expression->

wildmatch(fieldname, 'first value','second','ELMIRA,NY','Blank')

try ascending descending as per you want

hope this helps

Re: Issue with NULL/Blank records

Greg_Williams — Thu, 03 Jul 2014 11:29:48 GMT

Paramita - have you tried Trimming the field "trim(<field>)" ? Perhaps there is a space in there or some hidden character - find this to be the case sometimes reading in xls files.

Re: Issue with NULL/Blank records

Thu, 03 Jul 2014 11:33:46 GMT

Hi All

The problem is, no other values come up after blank when I sort it descending.

I also tried using - for nulls. Still the ELMIRA record doesnot come up.

Re: Issue with NULL/Blank records

Thu, 03 Jul 2014 11:45:42 GMT

I found the problem, I was restricting the list to Count(Distinct <Dim>).

Since there is a null value, it was restricting one record less as per my requirement.