Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
dadumas
Contributor II

do not trust nullasvalue

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. 

1 Reply
mikevwang
New Contributor III

Re: do not trust nullasvalue

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).