'0' as prefix

[#1]

Hi friends,

I have any code in the table in this format:

FieldA

00138081

00048002

09004222

000111

.....

I want to have the same numbers but without '0' as prefix

FieldA

138081

48002

9004222

111

.....

can you hel me??

my solution is num(FieldA) as FieldX

but there isn't another way?

thanks

best regards

g

[pat_agen · Accepted solution]

hi,

here is a link to a thread covering this point:

http://community.qlik.com/message/63300#63300

the answer could be this

replace(ltrim(replace(FieldA, '0', ' ')), ' ', 0)  as newField

it replaces zeroes with space, then removes leading space, thne replaces space with zeroes. Will work if your field does not contain a space to start with - unlikely in a numeric field with leading zeroes.

[#3]

Thank you for your suggestion

[#4]

There are several solutions:

replace(ltrim(replace(NumberText, '0', ' ')), ' ', 0)  as Number1

num(NumberText) as Number2

NumberText * 1 as Number3

[Miguel_Angel_Baeyens]

Hi,

At least in version 10 there's usually no such need of get rid of blanks, since the Num() function will work them around and will represent the value as numeric.

A couple of examples:

Num('   000345 ') + 3

will return 348

Num(     345   ) + 5

will work as well.

Num(   345.4 ) + .6

So I'd leave the Num() function instead of using more complex functions that can affect performance.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica