topic Re: Replacing Blank field with 'NA' in QlikView

Replacing Blank field with 'NA'

Wed, 25 Mar 2015 12:02:05 GMT

Hi All

Simple question i bet but i am drawing a blank today.

I have some data which has a number of blank fields (not null) and i want to replace the blank with a 'NA'.

I am using the following formula in my script but it doesn't seem to be working as the blanks are still appearing.

if(IsNull(ClaimOwner),'NA',ClaimOwner) as ClaimOwner2.1,

Any help would be great

Cheers

Andrew

Wed, 25 Mar 2015 12:05:21 GMT

You are checking only for the NULL. Probably you should be checking for the empty string as well like

if(IsNull(ClaimOwner) or ClaimOwner='','NA',ClaimOwner) as ClaimOwner2.1,

Wed, 25 Mar 2015 12:06:34 GMT

Also, you can check for the Null or empty values while loading the data in the Load script.

MK_QSL — Wed, 25 Mar 2015 12:06:57 GMT

If(IsNull(ClaimOwner) or Len(Trim(ClaimOwner)) = 0, 'N/A', ClaimOwner) as ClaimOwner

sunny_talwar — Wed, 25 Mar 2015 12:07:44 GMT

Try this:

If(Len(Trim(ClaimOwner)) = 0, 'NA', ClaimOwner) as ClaimOwner2.1

HTH

Best,

Sunny

maxgro — Wed, 25 Mar 2015 12:09:43 GMT

isnull() is for null

len(trim()) is for blank