Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to convert null as value?

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

3 Replies
buzzy996
Master II
Master II

try is way,

if(isnull(urfield),value,urfield)

wright the same way wherever u required.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

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]