topic Re: 'Show All Values' giving unexpected nulls. in QlikView

'Show All Values' giving unexpected nulls.

Mon, 17 Nov 2014 22:01:41 GMT

Newbie to QlikView, so apologies in advance if I'm doing things completely wrong..

I'm not sure why I'm getting nulls instead of field values. Here's a cut down example. First the script:

LOAD * INLINE [

ProdCode, ProdName

prod001, Cat

prod002, Dog

prod003, Mouse

prod004, Rat

];

LOAD * INLINE [

Day, ProdCode, Sales

Monday, prod001, 5

Monday, prod002, 10

Tuesday, prod002, 5

Tuesday, prod001, 6

Wednesday, prod001, 9

Wednesday, prod003, 3

];

I create a List Box and Straight Table (with 'Show all Values' selected for ProdCode). :

But when I select a 'Day' from the List Box - ProdCode and sun(Sales) columns give me what I want, but I get Nulls for ProdName (I want to see 'Mouse' and 'Rat' in ProdName in rows 3 and 4 in this example) ::

The problem persists even if I select all Days (in this case I want to see 'Rat' as a ProdName in Row 4) :

I would also like to remove the Null row at the bottom, but that's less important.

If I'm approaching this in completely the wrong way, please let me know.

Thanks for any assistance.

(I'm just trying out the QV free personal edition, so I'm not sure if I'll be able to load QVW files from others, so hopefully somebody can reply with a brief explanation.)

Re: 'Show All Values' giving unexpected nulls.

Anonymous — Tue, 18 Nov 2014 00:25:01 GMT

Check the Suppress When Value is Null checkbox on the Dimensions tab.

Re: 'Show All Values' giving unexpected nulls.

Tue, 18 Nov 2014 08:20:50 GMT

That doesn't solve the main problem.

I don't want to suppress the rows - I want them to show the ProdNames. See Image 2 and 3 above. (But it does remove the Null row at the end, which is good).)

Re: 'Show All Values' giving unexpected nulls.

Anonymous — Tue, 18 Nov 2014 14:23:45 GMT

I understand now.

There are a couple of ways around this:

Solution 1: Alter your existing expression to the following: Sum(Sales) + Sum({1} 0). The last part will generate zeros for the unselected items.

Solution 2: Add a new expression as Sum({1} 1). Then go to the Presentation tab, select the new expression and select the Hide Column radio button. This will calculate a value across all items whether selected or not, and simply hide the column.