Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
VB
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,...
HIC
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,...
HIC
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
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
Many thanks for helping me to approach the problem in a different way.