topic Re: Group Null values and click them in QlikView

Group Null values and click them

Thu, 19 Feb 2015 17:00:36 GMT

Hello all,

I have a graph where the dimension values are null for a lot of values. Is there a way I can group them all into say "null" or "unavailable" so that I can click on this bar and check the underlying data?

Re: Group Null values and click them

giakoum — Thu, 19 Feb 2015 17:04:50 GMT

yes in your reload script

if (isnull(dimension), 'N/A', dimension) as dimension

or maybe even use the setnull

Re: Group Null values and click them

MK_QSL — Thu, 19 Feb 2015 17:07:19 GMT

You can't just select Null Name in dimension.

use below in your chart/table to create a Calculated Dimension...

IF(IsNull(FIeldName) or Len(Trim(FIeldName))=0,'NoFieldName',FIeldName)

Re: Group Null values and click them

Thu, 19 Feb 2015 17:13:38 GMT

Hi Rav,

You have a few options, I would recommend converting your nulls to real selectable values in your script.

If you are looking for this on a single field, then as Ioannis says, you can just check for nulls on that field.

For a wider conversion on multiple fields you might want to consider map using.

Map_Nulls:

MAPPING LOAD

Null()

,'Null'

Autogenerate 1;

MAP [Field1],[Field2] USING Map_Nulls;

hope that helps

Joe

Re: Group Null values and click them

Thu, 19 Feb 2015 17:20:03 GMT

Hi manish,

Your solution helps me group but does not allow me to filter by clicking on the bar.

Re: Group Null values and click them

Thu, 19 Feb 2015 17:24:47 GMT

A calculated dimension based on null values won't be selectable in the chart no, you'll need a script change to convert your nulls to real values

Re: Group Null values and click them

MK_QSL — Thu, 19 Feb 2015 17:28:56 GMT

Please check enclosed file..

Re: Group Null values and click them

Thu, 19 Feb 2015 17:29:04 GMT

Joe,

Thank you for your reply. I would like to add that the dimension values are null because of join between two tables.

I am posting an example here. Can you please use this as a reference to help me figure it out?

Re: Group Null values and click them

MK_QSL — Thu, 19 Feb 2015 17:34:43 GMT

Change your script as below

Department:

Mapping LOAD * INLINE [

ProductID, Department

1, Computer

2, Books

3, Shoes

4, Supplies

];

LOAD *, ApplyMap('Department',ProductID,'UNKNOWN') as Department INLINE [

Transaction ID, ProductID

111, 2

222, 3

333, 4

444, 4

555, 3

666, 1

777, 2

888, 5

999, 6

];

Re: Group Null values and click them

Thu, 19 Feb 2015 17:39:56 GMT

Thank you Joe and Manish and ioannis. I wish I can mark all your answers as correct.