topic Re: Value won't Trim in QlikView

Value won't Trim

Fri, 22 Apr 2016 02:11:23 GMT

Hello - I'm trying to trim trailing spaces in a field where it contains ID:

Load:

trim([ID])

Value:

70138000000jzWsADM

for example the above has a space at the end and after the load, the space still exists!

Any help would be greatly appreciated - thank you!

-Jason

Re: Value won't Trim

tamilarasu — Fri, 22 Apr 2016 02:17:00 GMT

Jason,

Try,

Replace(ID,' ','')

PurgeChar(ID, chr(32) )

Re: Value won't Trim

sunny_talwar — Fri, 22 Apr 2016 02:17:32 GMT

What about this:

LOAD

PurgeChar(ID, ' '&Chr(32)) as ID

Re: Value won't Trim

Fri, 22 Apr 2016 02:22:30 GMT

Tried and didn't work - I found out a method in another thread to look for the chr of the space and it is actually a non-breaking space chr160 - so a purgechar worked instead.

Thanks Tamil!

Re: Value won't Trim

Fri, 22 Apr 2016 02:25:48 GMT

This was really close! I used =Ord(Right([ID],1)) and found out the chr was 160 instead of 32 and used

PurgeChar(ID,Chr(160)) as ID

Thanks again Sunny for the speedy response!

-Jason

Re: Value won't Trim

sunny_talwar — Fri, 22 Apr 2016 02:37:57 GMT

Awesome, that was a smart way to determine what the CHR was. I am going to keep that in mind for the future.

If you got what you wanted, please close this thread by marking your answer as correct and any other helpful responses.

Best,

Sunny

Re: Value won't Trim

Fri, 22 Apr 2016 02:40:19 GMT

Thanks for the reminder! Just marked as correct.

Re: Value won't Trim

tamilarasu — Fri, 22 Apr 2016 02:58:47 GMT

Glad you found the solution. Usually trim removes any leading and trailing spaces. I suspect something was there other than space and causing the problem. But I didn't thought about the non breaking space at the time.