topic Re: NULLASVALUE in QlikView

NULLASVALUE

Tue, 08 Sep 2015 07:06:01 GMT

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

Re: NULLASVALUE

sunny_talwar — Tue, 08 Sep 2015 07:11:12 GMT

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

Re: NULLASVALUE

avinashelite — Tue, 08 Sep 2015 07:16:44 GMT

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

Re: NULLASVALUE

Tue, 08 Sep 2015 07:16:53 GMT

PFA,

Re: NULLASVALUE

Tue, 08 Sep 2015 07:18:44 GMT

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.

Re: NULLASVALUE

Tue, 08 Sep 2015 07:19:03 GMT

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

Re: NULLASVALUE

Tue, 08 Sep 2015 07:23:02 GMT

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

Re: NULLASVALUE

Tue, 08 Sep 2015 07:33:14 GMT

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

Re: NULLASVALUE

sunilkumarqv — Tue, 08 Sep 2015 07:36:17 GMT

@@ !

Re: NULLASVALUE

Tue, 08 Sep 2015 07:48:05 GMT

Thanks a lot!