NULLASVALUE

[#1]

Hi All

I'm trying to use NULLASVALUE function to convert null value to selectable values. Please have a look at my app and source and advise what I'm missing.

Thanks

[sunny_talwar]

Can you share your script in text file? I am working with PE of the QlikView and won't be able to open the file you have shared.

Best,

Sunny

[avinashelite]

Hi Tumelo,

I checked your data, in your case its not the NULL values they are the missing values after the join

check this attachment

[#4 · Accepted solution]

PFA,

[#5]

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='R # ##0,00;R-# ##0,00';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET NullValue='<Unknown>';

NULLASVALUE *;

Trans:

LOAD Customer,

    Cost,

    NetSales,

    Volume

FROM

(ooxml, embedded labels, table is Facts);

Left Join

LOAD Customer,

    [Custome Name],

    Region,

    Branch

FROM

(ooxml, embedded labels, table is Customers);

The 2 tables a joining by Customer field. So because there's Cutomers in Fact table that do not exist in Customer table, Null values will be returned. I'm trying to make those selectable values.

[#6]

To Load the missing value as NULLASValue, just load data from resident load.

[#7]

Thanks a lot for the replies . But I'm still confused....why doesn't it work with JOIN?

[#8]

The Null values after join is not NULL value in data. It is missing values. If you are loading the data from resident table, then it will consider those values as NULL and replace with the NULLVALUE.

Refer the following link for more details about the NULL value.

NULL handling in QlikView

[sunilkumarqv]

@@ !

[#10]

Thanks a lot!