topic Re: Trouble treating NULLS as values. in QlikView

Trouble treating NULLS as values.

lsteere01 — Thu, 28 Mar 2013 16:08:29 GMT

I'm having trouble getting all null values converted to usable values so they can be selected on a chart. I've tried setting NullInterpret, NullDisplay, NullAsValue, NullValue but they don't make any difference. The following code catches most of them but as you can see on the chart there are 191 that are slipping through and are still null.

LAST_VALUES:

LOAD
PAT_ID,
A1C_RESULT_DATE as [LAST_A1C_RESULT_DATE],
A1C_ORD_VALUE as [LAST_A1C],

// Create A1C aging bands
if (Num(Today()) - Num(A1C_RESULT_DATE) > 730, '2+ Yrs',
if (Num(Today()) - Num(A1C_RESULT_DATE) > 365, '1+ Yrs',
if (Num(Today()) - Num(A1C_RESULT_DATE) > 182, ' 6+ Mos', ' < 6 Mos'))) as A1C_AGING,

// Create the A1C Value bands
if (IsNull(A1C_ORD_VALUE), '-NO A1C', Floor(A1C_ORD_VALUE)) as A1C_GROUP

RESIDENT ALL_A1C
WHERE A1C_ROWNUM = 1;

I JOIN several other QVDs to LAST_VALUES then use the following code to put values in NULL fields

--------------------------------

NULLMAP:

MAPPING LOAD
Null(), '-NONE'
AUTOGENERATE 1;

MAP A1C_GROUP USING NULLMAP;
RIGHT JOIN (LAST_VALUES)
LOAD DISTINCT *
RESIDENT LAST_VALUES;

Re: Trouble treating NULLS as values.

Thu, 28 Mar 2013 16:25:47 GMT

along with IsNull(), use Len(Trim(Field))<>0.

Hope this helps.

Re: Trouble treating NULLS as values.

Thu, 28 Mar 2013 16:29:25 GMT

Here is the technical brief about Null. Hope this document helps you.

Re: Trouble treating NULLS as values.

lsteere01 — Thu, 28 Mar 2013 17:53:48 GMT

Unfortunatetly that doesn't make any difference.

Re: Trouble treating NULLS as values.

lsteere01 — Thu, 28 Mar 2013 17:56:39 GMT

Thanks for the link. I actually read that document yesterday when I was looking for an answer but I still haven't found the solution to those 191 hold-outs.