topic Re: Trim Excel Column Data in QlikView

Trim Excel Column Data

Tue, 27 Aug 2013 10:53:22 GMT

Dear Folks,

I have a scenario , where i need to perform cleansing on an excel column.

I believe Location columns contain spaces in it , but when i perform trim, purgechar operations. It fails to remove the spaces.

Have attached the sample files.

Regards,

Vasim

MayilVahanan — Tue, 27 Aug 2013 11:03:19 GMT

Try like this

KeepChar(Trim(Location),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as Location,

Tue, 27 Aug 2013 11:49:38 GMT

Use PurgeChar(Location, chr(32) & chr(160))

In location files, sometimes it is used the ascii 160 as space.

tresB — Tue, 27 Aug 2013 11:56:39 GMT

As said by Juan, this is because of non-breaking space character represented by ascii 160. So the following should work for you:

Purgechar(Location, chr(160)) as NewLocation.

Sat, 31 Aug 2013 09:24:03 GMT

Thanks a lot mayil, juan and tresesco.

I managed to get rid of non breaking space by using Purgechar(Location, chr(160)).