topic Re: Replacing null values in star scheme based table in QlikView

Replacing null values in star scheme based table

straesser — Thu, 11 Jan 2018 09:51:08 GMT

Hi all,

I'm facing the following display issue for null values and hope that one of you has an idea how to solve it.

We use left keep to enrich our main data table Main with additional information Add. This gives us a star scheme (several tables with different types of additional information). Key in my example is the Factory.

Main:

RecordNo	Factory	Figure
1	London	42
2	Paris	142
3	Rome	242
4	Atlantis	342
5	Madrid	442

Add:

Factory	Address
London	Buckingham Palace
Paris	Eiffel tower
Rome	Collosseum
Madrid

There are cases where we don't have additional information in Add for a record in Main. This gives us null values.

In my example this would be for factory Atlantis.

If we now create a pivot table in the user front end showing Figure and Address Madrid would be shown as blank whereas Atlantis is shown as null in the Address column. Our customer wants us to show both in one line summing up the figures (Address = blank with Figure = 784). I have a lot of such tables so I would prefer to solve it somewhere centrally, e.g. in the load statement.

Any idea how I can set null values blank in this case?

I tried NullAsValue but without any success. I guess it's because we use the star scheme as a basis for our frontend instead of concatenating all fields into one table (for memory reasons). I also failed with any attempt of mapping because I don't find anything to map 🙂 But maybe I approached it in the wrong way.

Thanks in advance for your support,

Sabine

Re: Replacing null values in star scheme based table

petter — Fri, 12 Jan 2018 10:08:11 GMT

Hi Sabine,

You could do it like this:

I have attached the example application too...

Re: Replacing null values in star scheme based table

straesser — Tue, 16 Jan 2018 08:58:45 GMT

Hi Petter,

thank you for your feedback! Yes, this would work for my existing charts. Unfortunately my customer is doing own analysis using own list boxes, tables etc. And in the list boxes he is not able to select the null values. Sorry, I should have made that clear in my post. This is why I would prefer to do it somewhere centrally in the data itself. Do you have any idea?

Sabine

Re: Replacing null values in star scheme based table

straesser — Tue, 16 Jan 2018 08:59:30 GMT

Btw, I don't see any attachment

Re: Replacing null values in star scheme based table

petter — Tue, 16 Jan 2018 10:32:18 GMT

Qlik Sense is most of the time best used with a so-called Dimensional Model approach for the data.

In this respect your Main table is the fact table and the Add table is a dimension table. According to best practices in the Dimensional Modeling you should have an entry for every dimensional value that you have in your fact table in the dimension table (referential integrity). So if there are no corresponding entry in the dimension table it is important to populate it with a dummy/not existing/unknown value that could for instance in your case say "<unkown address>" for Atlantis and should probably say the same thing for Madrid as a blank would be an unknown/unspecified address too.

Null values are more often than not a pain in the neck. So getting rid of them and rather have something more meaningful that you can actually select is better. This way you will also be able to select them in List Boxes and elsewhere in a more natural way.

You can add rows to the Add table with this extra load:

LOAD DISTINCT

Factory,

'(unkown address)' AS Address

RESIDENT

Main

WHERE

Not( Exists( Factory ) );

In the load of the Main table you can replace any blank address with the text (unknown address) too like this:

Main:

LOAD

Factory,

If( Trim( Address ) = '' , '(unknown address)' , Address ) AS Address,

.....

;

Re: Replacing null values in star scheme based table

petter — Wed, 17 Jan 2018 10:36:27 GMT

Don't know why you don't see any attachment - I can see it as a paperclip and document at the end of my response/comment...