topic Re: show Unknown if IsNull(@48) or @48=' ' in QlikView

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

Anonymous — Mon, 12 Nov 2012 07:43:12 GMT

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?

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

Gysbert_Wassenaar — Mon, 12 Nov 2012 07:47:48 GMT

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

edit: fixed typo in Debtor Type

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

Anonymous — Mon, 12 Nov 2012 07:50:52 GMT

Hi Gysbert,

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

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

jonathandienst — Mon, 12 Nov 2012 07:53:32 GMT

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

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

jonathandienst — Mon, 12 Nov 2012 07:54:21 GMT

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

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

Anonymous — Mon, 12 Nov 2012 08:05:08 GMT

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],

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

Anonymous — Mon, 12 Nov 2012 08:09:17 GMT

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.

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

Anonymous — Mon, 12 Nov 2012 09:05:50 GMT

hi chris,

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

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

Anonymous — Mon, 12 Nov 2012 09:36:35 GMT

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