topic Re: remove 0 in QlikView

remove 0

Fri, 21 Sep 2012 11:40:31 GMT

Hi,

I have data like following.

How can i remove all the 0 before the real digits?

Thanks.

000000001234

000000334456

000000043467

Re: remove 0

sivarajs — Fri, 21 Sep 2012 11:51:08 GMT

use floor(fieldname) or num(fieldname)

Re: remove 0

Fri, 21 Sep 2012 11:55:14 GMT

Hi,

you can also try

purgechar(000000001234,'0')

Re: remove 0

sivarajs — Fri, 21 Sep 2012 12:06:11 GMT

@keeruthi Purgechar is not a good idea because

000001234-->1234

0000012034-->1234

You'll get wrong value

Re: remove 0

Fri, 21 Sep 2012 12:10:07 GMT

@Siva,

tht's wht he wants right??

"How can i remove all the 0 before the real digits?" --to remove zero's before the digits..,

i understood the problem in that way 🙂

Re: remove 0

Fri, 21 Sep 2012 12:17:48 GMT

Hi All,

Good ideas ! n

Non works though.

Any other ways?

Re: remove 0

CELAMBARASAN — Fri, 21 Sep 2012 12:37:03 GMT

Try with Num(FieldName)

Re: remove 0

Fri, 21 Sep 2012 12:52:29 GMT

This is strange...

i created a test qvw, with some inline data, n i type in the data pattern.

then i apply num(), it works !

BUT, when i extract the code form SAP. and i do num(). it doesn't.

nor... floor(num()) or purgechar(num()) or num(num())

anymore advises?

Re: remove 0

matt_crowther — Fri, 21 Sep 2012 14:46:43 GMT

Nick,

num() seems to work with me - not against SAP data though.

As a 'ye haw!!' cowboy workaround I'd try something like the following in the load script to chop the field down to size:

right(Number,len(Number)-(FindOneOf(Number,'123456789'))+1)

Hope that helps,

Matt

@QlikviewBI

Re: remove 0

CELAMBARASAN — Fri, 21 Sep 2012 14:54:05 GMT

May be SAP adds some char to the string data

Try this

Num(KeepChar(FieldName,'0123456789'))

Hope it may solves the problem

Celambarasan

Re: remove 0

Fri, 21 Sep 2012 15:04:53 GMT

Hi Celambarasan,

Num(KeepChar(FieldName,'0123456789')) returns -

Num#(KeepChar(FieldName,'0123456789')) returns 000000001234

I'm now trying the cowboy workaround....

Re: remove 0

Fri, 21 Sep 2012 15:06:38 GMT

Mattew, i was thinking, since data from SAP is abnormal.

may be i can load it into txt, then from txt back to QV for num() ?

i tried your formula, but keep throwing an error saying field not found...

Re: remove 0

Fri, 21 Sep 2012 15:15:44 GMT

Matt, ignore my previous post.

your ye haaa cowboy solution works wonder.

Thanks!

Re: remove 0

matt_crowther — Fri, 21 Sep 2012 15:19:03 GMT

Nick,

I'm suprised the formula throws an error - it works fine for me in testing. Try this amended version, it takes into account Celambarasan's suggestion too to hopefully remove any dodgy characters:

right(keepchar(YourField,'0123456789'),len(YourField)-(FindOneOf(YourField,'123456789'))+1) as Trimmed_Number

Where you replace 'YourField' with the name of the field you're trying to trim.

All the best,

Matt

@QlikviewBI

Re: remove 0

Fri, 21 Sep 2012 16:01:08 GMT

Matt.. it was my fault.

But it's working now.

Thanks.