do not trust nullasvalue

dadumas — Wed, 17 Oct 2018 21:03:48 GMT

This post is a tip to avoid a lot of frustration. In all but the simplest of qlik views, we all use concatenated facts. nullasvalue DOES NOT handle setting values across concatenated facts in which some columns exist in one data set and not another.

For example:

fact_main:

Load

SalesMetric exists only in the first data set

...

concatenate (Fact_Main)

Load

SalesOtherMetric exists only in the second data set

...

defining nullasvalue on both of these columns to 0, up front prior to the load does not set them to 0 when data loads for columns that do not exists accross data sets.

So a measure defined as sum(SalesMetric + SalesOtherMetric) will compute to 0 (bad), since nullasvalue "does not work" as you might think.

Best to avoid ever using nullasvalue, and explicitly set columns that do not exist in each others dataset to 0, or whatever value they need.

Ex. The best practice way:

fact_main:

Load

SalesMetric exists only in the first data set

0 as SalesOtherMetric

...

concatenate (Fct_Main)

Load

SalesOtherMetric exists only in the second data set

0 as SalesMetric

...

Now, sum(SalesMetric + SalesOtherMetric) will compute properly.

Re: do not trust nullasvalue

mikevwang — Wed, 17 Oct 2018 22:03:19 GMT

It's also possible to map nulls to a default value instead. It works for nulls created from concatenations and joins.

ZeroDefault:

Mapping Load

Null(), 0

Autogenerate 1;

Map SalesOtherMetric Using ZeroDefault;

Map SalesMetric Using ZeroDefault;

Then load your data after (non-optimized if from qvd).

topic Re: do not trust nullasvalue in QlikView

do not trust nullasvalue

Re: do not trust nullasvalue