topic Re: Making NULL Selectable in Straight Table. in QlikView

Making NULL Selectable in Straight Table.

Thu, 03 Sep 2015 15:52:16 GMT

Hi,

I have a straight table with a calculated dimension column. The column just does a simple count. However, for rows where the count is zero, a "-" (i.e., NULL) is displayed instead. How would I make the "-" display "0" instead and also make it selectable so that I can filter by only those rows that have a zero count?

Thanks,

Kenny

Re: Making NULL Selectable in Straight Table.

Thu, 03 Sep 2015 16:02:45 GMT

Hi,

Post some example.

Re: Making NULL Selectable in Straight Table.

Thu, 03 Sep 2015 16:14:32 GMT

The expression for my calculated dimension (this is a column in a straight table) is:

=AGGR(Count(DISTINCT {<[Status Type] = {"CON"},[Action Type] = {"INS","UPD"}>}[Partner ID]),[Product Id])

So I see I can change the Null Symbol to whatever I want ("0" in this case). Now I just need to figure out how to make the zero selectable. Also, why can't I sort this column by descending/ascending order by double-clicking the column heading like I can for other dimensions? Thanks.

Re: Making NULL Selectable in Straight Table.

Thu, 03 Sep 2015 16:20:08 GMT

Nevermind about the sort. I had no check boxes selected in the Sort tab for that dimension for some reason. Just need to figure out how to make NULL=0 and make it selectable.

Re: Making NULL Selectable in Straight Table.

Thu, 03 Sep 2015 16:24:06 GMT

in the Presentation tab, you can display Null and missing values how you want and can change the '-' to '0'.

Re: Making NULL Selectable in Straight Table.

Oleg_Troyansky — Thu, 03 Sep 2015 16:25:01 GMT

Hi Kenny,

So, I think you already discovered by now that you can substitute the NULL symbol with any other symbol in the Presentation tab of the Chart Properties.

Generally speaking, NULL values cannot be selected, however in your case, selecting the NULL values in your calculated dimension will lead to selecting the corresponding Product IDs - the dimensions that you use in your AGGR. If you don't see it happening, please post a sample and we will take a look.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Making NULL Selectable in Straight Table.

Thu, 03 Sep 2015 16:37:09 GMT

Thanks, Oleg. Unfortunately, selecting the "0" does not select all corresponding product IDs with "0" (i.e., NULL) value. I found this syntax from set blanks field to numeric null i.e 0:

if(isnull(expression),0,expression)

Would you know how to incorporate this into the syntax I provided above somehow?

Re: Making NULL Selectable in Straight Table.

Oleg_Troyansky — Thu, 03 Sep 2015 16:45:49 GMT

Just like that - replace the word "expression" with your formula (remove the equal sign in front of the expression).

Re: Making NULL Selectable in Straight Table.

Thu, 03 Sep 2015 16:50:50 GMT

if(isnull(AGGR(Count(DISTINCT {<[Status Type] = {"CON"},[Action Type] = {"INS","UPD"}>}[Partner ID]),[Product Id])),0,AGGR(Count(DISTINCT {<[Status Type] = {"CON"},[Action Type] = {"INS","UPD"}>}[Partner ID]),[Product Id]))

I believe this is correct - replace 'expression' with your actual expression in the 2 places

Re: Making NULL Selectable in Straight Table.

Thu, 03 Sep 2015 16:57:54 GMT

Yes, that worked! Thanks so much for everyone's responses...really appreciate the help.