show Unknown if IsNull(@48) or @48=' '

[Anonymous]

I used the following:

(if(isnull(@48)='','Unknown',

     if(@48='','Unknown',@48))) AS [Debtor Type],

but it's not picking up all the blank DebtorType.

aka I'm still getting values coming in as blank.

Any ideas why it's not picking up all the blanks?

[Gysbert_Wassenaar]

try: if(len(trim(@48))=0,'Unknown',@48) as [Debtor Type]

edit: fixed typo in Debtor Type

[Anonymous]

Hi Gysbert,

I tried that, but it didn't work out. Nothing came back at all for DebtorType.

[jonathandienst]

Hi

Its not working because IsNull() returns a true (-1) or false (0) value. Gysbert's suggestion should work as the length (len) of a null or an empty string is 0.

Regards

Jonathan

[jonathandienst]

[DebtorType] or [Debtor Type]? Did you correct the field name typo in Gysbert's suggestion?

[Anonymous]

Hi Jonathan,

when I use Gysbert's script this is the result I get.

this is the result when I use the script:

(if(isnull(@48)=' ','Unknown',

     if(@48= ' ','Unknown',@48))) AS [Debtor Type],

[Anonymous]

John,

thanks for noticing the that [DebtorType] need to be changed to [Debtor Type]

when I made that change it is giving the same result as the second table above.

[Anonymous]

hi chris,

                 Can u provide an app? it wud b easy to rectify...

[Anonymous]

Well I think you can't use isnull function the way you used.

(if(isnull(@48)='','Unknown',

     if(@48='','Unknown',@48))) AS [Debtor Type],

the correct format of isnull is :

(if(isnull(@48),'Unknown',

     if(@48='','Unknown',@48))) AS [Debtor Type],

or you can use this format :

(if(@48=null(),'Unknown',

     if(@48='','Unknown',@48))) AS [Debtor Type],

I hope this helps!

MultiView