How to replace a value with space string to null

[random_user_3869]

Hello,

I have several field from some databases that have the following issue:

there are some field values in my script  with empty values :

-

it's empty cell basically that i want to consider those values as null but if i do let's say if len(Field A) = 0, Null()

it doesn't work because the string value can ba ' ' or '  ' or '     '. BAsically varchar (2) or varchar 40.

Anyone can help me with that.

Thank you

[chris_djih]

try trim(FieldA) - this deletes all spaces.

if any other character neds to be deleted use: purgechar(FieldA,'#') to delete every '#' in the FieldA

[vikasmahajan]

Hi,

while loading script check with  [Value A] :

If(IsNull([Value A]) or len([Value A]) =0, Null_value, [Value A]) As [Value A] 

use Null_value in set analysis to filter null values.

Reference : 

https://community.qlik.com/t5/QlikView-Documents/NULL-handling-in-QlikView/ta-p/1484472

https://www.johndaniel.com/when-values-arent-values-null-handling-in-qlik/

Hope this help.

Vikas

[random_user_3869]

The issue is that the space (trim doesnt work) can have a lenght of 20 30 or 40 or more depending on the field value)

So the issue is that i need to make a condition and i don't know how to do it.

Because the field value can be : '' or '   ' or  '   '

[chris_djih · Accepted solution]

Then i would go with the purgechar() method. You can use Chr(32) as character code for space instead of ' '. Another space-character is chr(160).
So your load statement shoul llook like this:

PurgeChar(PurgeChar([ValueField], chr(32)),chr(160))  as [CleanValue]

There should be no more spaces in the [CleanValue] field

[random_user_3869]

Thank you it worked !!