topic Re: How to replace a value with space string to null in App Development

How to replace a value with space string to null

random_user_3869 — Wed, 19 Jan 2022 11:01:42 GMT

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

Re: How to replace a value with space string to null

chris_djih — Wed, 19 Jan 2022 12:33:49 GMT

try trim(FieldA) - this deletes all spaces.

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

Re: How to replace a value with space string to null

vikasmahajan — Wed, 19 Jan 2022 14:02:38 GMT

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

Re: How to replace a value with space string to null

random_user_3869 — Wed, 19 Jan 2022 14:48:36 GMT

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 ' '

Re: How to replace a value with space string to null

chris_djih — Wed, 19 Jan 2022 15:59:01 GMT

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

Re: How to replace a value with space string to null

random_user_3869 — Fri, 21 Jan 2022 17:06:22 GMT

Thank you it worked !!