topic Re: Cutting off leading zeros in QlikView

Cutting off leading zeros

peschu123 — Thu, 16 May 2013 08:40:37 GMT

Hi,

I have a problem with some fields, having leading zeros.

For example a field like OrderNo..

In source 1 the OrderNo. looks like: 00001234567, 00012345678

In source 2 the OrderNo. looks like: 1234567, 12345678

Qlikview don't seem to have a problem with that, but is there a solution to cut off the leading zeroes or to define a lenght e.g. of 14 and fill with zeros?

Or is it unnecessary to care about this?

Thanks for any advice.

- PS -

Re: Cutting off leading zeros

sushil353 — Thu, 16 May 2013 09:02:33 GMT

Hi,

if you want to make the orderno in same format then try Repeat function:

Below is the sample scirpt:

T1:

load * Inline

[

value

1234567

12365547

];

T2:

LOAD

Repeat(0,14-len(value))&value as testvalue

Resident T1;

and other way is using NUM(value): it will remove the zero's.

HTH

Sushil

Re: Cutting off leading zeros

sivarajs — Thu, 16 May 2013 09:02:33 GMT

try using num(fieldname)

Re: Cutting off leading zeros

er_mohit — Thu, 16 May 2013 09:07:28 GMT

num(num#( OrderNo))

hope it helps

Re: Cutting off leading zeros

Thu, 16 May 2013 09:21:06 GMT

pick(wildmatch(OrderNo,'1*'),'1*') as OrderNo_new

Re: Cutting off leading zeros

peschu123 — Thu, 16 May 2013 10:54:10 GMT

Hi vishwaranjan,

thank you for your reply!

I didn't had time to check the solutions, yet. But when I interpret your solution right, than this does not work with 00007654321? "12345678" was just an example the "orderno" can begin with any number...

Anyway, num(fieldname) did the trick.

Thank you very much for the fast replies.

- PS -