topic how to convert null as value? in QlikView

how to convert null as value?

Mon, 14 Dec 2015 13:33:40 GMT

hi by using nullasvalue i convert null values into numeric value but by using if condition in the script i want to convert in Title column 100 = MR like this and in first_name 100= m like this and in last_name 100= jani like this, gender 100 = male or female.

set nullvalue= '100';

NULLASVALUE *;

LOAD TITLE,

"FIRST_NAME",

"LAST_NAME",

GENDER;

SQL SELECT *

FROM *****

Thanks

Madhu

Re: how to convert null as value?

buzzy996 — Mon, 14 Dec 2015 13:39:24 GMT

try is way,

if(isnull(urfield),value,urfield)

wright the same way wherever u required.

Re: how to convert null as value?

Peter_Cammaert — Mon, 14 Dec 2015 13:47:40 GMT

Or alternatively, the preceding load might become something like this (takes into account empty fields which are also not very nice)

LOAD IF (len(trim(TITLE)) > 0, TITLE, 'MR') AS TITLE,

IF (len(trim(FIRST_NAME)) > 0, FIRST_NAME, 'm') AS FIRST_NAME,

IF (len(trim(LAST_NAME)) > 0, LAST_NAME, 'jani') AS LAST_NAME,

IF (len(trim(GENDER)) > 0, GENDER, 'male or female') AS GENDER,

SELECT ...

Remove the NULLASVALUE statement.

Best,

Peter

Re: how to convert null as value?

Anonymous — Mon, 14 Dec 2015 13:48:54 GMT

not sure you want like this?

IF(TITLE='100', 'Mr,', TITLE) as TITLE,

IF([FIRST_NAME]='100','M', [FIRST_NAME]) as [FIRST_NAME],

IF([LAST_NAME]='100','Jani', [LAST_NAME]) as [LAST_NAME]