topic Re: Convert Oracle 'NULL' to Qlikview Null in QlikView

Convert Oracle 'NULL' to Qlikview Null

Anonymous — Wed, 28 May 2014 08:36:08 GMT

Hi,

I have the below query which returns a few NULL values in the CMTT_APPROVER_NAME column - I need to suppress these NULL values from being displayed in a dropdown of fields, so that the dropdown will list only the non-NULL CMTT_APPROVER_NAME field values. Here's the Qlikview LOAD and Oracle queries:

LOAD Distinct

"Business Line",

if(IsNull(CMTT_APPROVER_NAME),null(),CMTT_APPROVER_NAME) AS "Committee Approver";

SQL SELECT DISTINCT

"Business Line",nvl(CMTT_APPROVER_NAME,null) AS CMTT_APPROVER_NAME

FROM

(SELECT DISTINCT

cd.committee_id as "Committee ID",

cd.business_line as "Business Line",

(SELECT EMP_FULL_NAME

FROM ....

WHERE CD.CMTT_APPROVER = EMPLOYEE_NO

AND CURRENT_RECORD_FLG = 1

) AS CMTT_APPROVER_NAME

FROM ....

WHERE .....

AND ....

);

So, I am expecting the if(IsNull(CMTT_APPROVER_NAME),null(),CMTT_APPROVER_NAME) AS "Committee Approver" line in the above LOAD to return the Qlikview NULL value and therefore suppress the instances where the CMTT_APPROVER_NAME field is NULL from the dropdown - however, my dropdown contains the string NULL for those NULL values (please see attached screenshot).

If I replace

if(IsNull(CMTT_APPROVER_NAME),null(),CMTT_APPROVER_NAME) AS "Committee Approver"

with

if(IsNull(CMTT_APPROVER_NAME),'TEST',CMTT_APPROVER_NAME) AS "Committee Approver"

then the NULL string is replaced with TEST, so I am confident that IsNull() is finding the NULL instances correctly, but the null() Qlikview function is not replacing those NULLS with the Qlikview NULL value and therefore, they are not being suppressed from the dropdown (it's a search object). How can I convert those NULL instances in a way that will suppress them from the dropdown and any other objects within which I display the CMTT_APPROVER_NAME field (I know various charts have the option to suppress NULL values but the Search object does not)? I know I can replace null() with an obvious string such as 'No Name Found', but that is not what the user wants.

Hope you can help,

Re: Convert Oracle 'NULL' to Qlikview Null

hic — Wed, 28 May 2014 08:49:54 GMT

The question is - where does the string 'NULL' come from?

There is nothing in QlikView that replaces <NULL> with the string 'NULL'. <NULL> is normally loaded as <NULL>, i.e. as a lack of value, also from a database. So, there is no "convert from DB NULL to QlikView NULL". It is the same thing - NULL is NULL.

Further, true <NULL>s are never visible in a list box. So,...

Is there anything in your DB connection (e.g, the ODBC settings) that could replace <NULL> with the string 'NULL' ?
Do you have any other commands in the QlikView script that could alter NULL management, e.g. 'Set NullDisplay' or 'Set NullInterpret'?

HIC

Re: Convert Oracle 'NULL' to Qlikview Null

Wed, 28 May 2014 09:01:39 GMT

Hi,

In the SQL part :

,nvl(CMTT_APPROVER_NAME,null) AS CMTT_APPROVER_NAME

That's where nulls are replaced by 'NULL' string. It seems QV doesn't interpret ,null) as it would do in Oracle.

I assume you would have other problems without the nvl(). Then, you can just use this in the LOAD part to get back real nulls:

if(CMTT_APPROVER_NAME<>'NULL',CMTT_APPROVER_NAME) AS "Committee Approver"

Hope this helps

Re: Convert Oracle 'NULL' to Qlikview Null

hic — Wed, 28 May 2014 09:07:33 GMT

Just for clarity: The nvl() function is not evaluated by QlikView. It is evaluated by the ODBC driver, i.e. by Oracle. Apart from that, I think your solution is a good one.

HIC

Re: Convert Oracle 'NULL' to Qlikview Null

Anonymous — Tue, 03 Jun 2014 07:48:23 GMT

Many thanks for helping me to approach the problem in a different way.