topic Re: Remove Special Characters in QlikView

Remove Special Characters

prees959 — Thu, 21 Sep 2017 09:05:23 GMT

Hi,

I am using this formula in Excel to remove a leading white space :

TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))

Is there a similar function to do this in Qlikview. I have tried a simple LTRIM but this doesn't seem to work.

Many thanks.

Phil

Re: Remove Special Characters

YoussefBelloum — Thu, 21 Sep 2017 09:20:03 GMT

Hi Phil,

Can you try some of these:

Replace(Field,' ','')
TRIM(REPLACE(Field,' ',''))
Purgechar(Field,' ')

You can try to combine these functions also.

Good Luck

Re: Remove Special Characters

its_anandrjs — Thu, 21 Sep 2017 09:21:23 GMT

You can use

=Trim( FieldName )

Re: Remove Special Characters

sunny_talwar — Thu, 21 Sep 2017 12:32:01 GMT

Try this

PurgeChar(A2, Chr(160)&Chr(32))

Re: Remove Special Characters

ashwinishinde — Thu, 21 Sep 2017 12:39:42 GMT

Try this:

PurgeChar(A2, Chr(160)&Chr(32))

thanks,

ashwini

Re: Remove Special Characters

Peter_Cammaert — Thu, 21 Sep 2017 12:41:26 GMT

How about this:

Trim( Replace( A2, Chr(160), ' ' ))

Check the examples on the pages that each function links to.

Re: Remove Special Characters

Peter_Cammaert — Thu, 21 Sep 2017 13:25:56 GMT

An even more complete reproduction of the Excel function Trim uses MapSubString to replace series of spaces. But this is only usable in your Load Script...

MapMultiSpace:

MAPPING

LOAD Repeat( ' ', RowNo()+1) AS Index, ' ' AS Replacement

AUTOGENERATE 9;

LOAD

Trim( MapSubString‌( 'MapMultiSpace', Replace( A2, Chr(160), ' ' ))) AS CorrectedA2,

FROM ...;

Best,

Peter

Re: Remove Special Characters

joydipp1988 — Thu, 13 Sep 2018 11:08:40 GMT

Hi Sunny,

What are the characters defined by Chr(160) and Chr(32) ?

Is that spaces or any special characters?

Any link you to get more info on this?

Thanks,

Joy

Re: Remove Special Characters

nsetty — Thu, 13 Sep 2018 11:38:59 GMT

chr(32) is space.

chr(160) is non-breaking space.

Re: Remove Special Characters

Peter_Cammaert — Thu, 13 Sep 2018 11:55:28 GMT

If you want to find out about other characters represented by numbers between 0 and 65535, check any on-line Unicode table. Like this one:

https://unicode-table.com/en/

Re: Remove Special Characters

joydipp1988 — Thu, 13 Sep 2018 13:28:23 GMT

Thanks Nagesh for the info.

Re: Remove Special Characters

joydipp1988 — Thu, 13 Sep 2018 13:29:33 GMT

Thanks Peter for the info. Actually I was looking for this kind of site.

Thanks.