Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hkg_qlik
Creator III
Creator III

Subfield

Hi,

I have a customer table holding the information for all the departments:

Department:

ABC - 1234

XYZ - 1234456

Null

QVW - 98712

I am extracting the fund information from the department as:

SubField(Department,'-',1) as Fund

Everything works fine here but I would like to rename Null as 'N/A' when I extract the fund information using

SubField(Department,'-',1) as Fund,

Regards,

H

1 Solution

Accepted Solutions
Not applicable

Try If(Len(Department) <> 0, SubField(Department,'-',1), 'N/A') as Fund,

View solution in original post

5 Replies
swuehl
MVP
MVP

Try

replace(subfield(Department,'-',1),'Null','N/A') as Fund

hkg_qlik
Creator III
Creator III
Author

It is not working. It still displays as empty field.

jeffmartins
Partner - Creator II
Partner - Creator II

Hi

You can use isnull() function to do this

if ( not isnull(Department), subfield(Department,'-',1), 'N/A'as Fund

Regards

Not applicable

Try If(Len(Department) <> 0, SubField(Department,'-',1), 'N/A') as Fund,

johnw
Champion III
Champion III

Works fine for me IF by 'Null' you mean the string 'Null'.  So I'm betting you meant the field is actually null.  Then perhaps this:

if(len(subfield(Department,'-',1)),subfield(Department,'-',1),'N/A') as Fund

Is there a string version of the alt() function?  That would be simpler if so, but I'm not aware of one and don't see anything promising in the list of string functions.  Maybe there's some other way to simplify it, though.

Edit: I was too slow with my post, and indeed my expression was more complicated than it needed to be.  You can slightly simplify what Ducati 888 SP5 wrote, though, since 0 is false:

if(len(Department),subfield(Department,'-',1),'N/A') as Fund

Though I suppose that if the value of Department was '- Something', you might need my more-complicated version.  I'm guessing you don't have data like that, though.