topic Re: Remove Preceding 0 from script level in QlikView

Remove Preceding 0 from script level

arvind_patil — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have one GL FIeld in that values are like below

0011101033

08546880

000111222333

Required Output

11101033

8546880

111222333

Note : I have checked with Num and Num # Function .

Thanks,

Arvind Patil

Re: Remove Preceding 0 from script level

micheledenardi — Fri, 17 Nov 2017 09:27:06 GMT

Replace(LTrim(Replace(YourField, '0', ' ')), ' ', '0') as [YourFieldWithoutLeadingZeros]

Re: Remove Preceding 0 from script level

Chanty4u — Fri, 17 Nov 2017 09:29:05 GMT

its working for me

load * ,num(aaa) as new;

load * Inline

[

aaa

0011101033

08546880

000111222333

];

Re: Remove Preceding 0 from script level

Chanty4u — Fri, 17 Nov 2017 09:29:56 GMT

Re: Remove Preceding 0 from script level

Chanty4u — Fri, 17 Nov 2017 09:32:11 GMT

or try this

LOAD *, Number8digit+0 As Number;

LOAD * Inline [

Number8digit

00000978

00001524

00001024

00012356

];

Re: Remove Preceding 0 from script level

Chanty4u — Fri, 17 Nov 2017 09:33:43 GMT

this also you can try

Load

NUMBER,

Num(NUMBER) as NUMBER1,

NUM(Right(NUMBER, Len(NUMBER)-FindOneOf(NUMBER, '123456789')+1)) as NUMBER2

Inline

[

NUMBER

00000978

00001524

00001024

00012356

];

Re: Remove Preceding 0 from script level

arvind_patil — Fri, 17 Nov 2017 09:50:18 GMT

No its not working for me.

Thanks,

Arvind Patil

Re: Remove Preceding 0 from script level

PrashantSangle — Fri, 17 Nov 2017 09:55:27 GMT

can you share sample file or screen shot of what are you trying???

Regards,

Prashant

Re: Remove Preceding 0 from script level

arvind_patil — Fri, 17 Nov 2017 09:56:46 GMT

Hi Michele,

Thanks for prompt reply it works for me .

But I am very enthusiastic to understand why num and num # not worked.

Thanks,

Arvind Patil

Re: Remove Preceding 0 from script level

arvind_patil — Fri, 17 Nov 2017 10:20:29 GMT

HI Chanty,

I tried your answers but it wont work for me.

Thanks,

Arvind Patil

Re: Remove Preceding 0 from script level

Chanty4u — Fri, 17 Nov 2017 10:29:22 GMT

oh is it ? its working fine for me

Re: Remove Preceding 0 from script level

Chanty4u — Fri, 17 Nov 2017 10:36:18 GMT

try this

Num(Num#(Field,'#,##0'),'#,##0') AS FieldName,

Re: Remove Preceding 0 from script level

micheledenardi — Fri, 17 Nov 2017 11:19:19 GMT

I think it depends from the fields type (numeric or string) but the only way to verify it is to connect directly to the source database and do some check.

During last year I've tried different (lots) ways to remove leading zeros and the ones i've shared is the best because it works with both numeric and string field types and it's conceptually easy to understand.

Re: Remove Preceding 0 from script level

micheledenardi — Fri, 17 Nov 2017 11:23:20 GMT

It's not reliable to use a sample file (excel,txt, csv...) instead a db source because during these exports the fields type change so the same qlik script could be returns different resuls.

Re: Remove Preceding 0 from script level

arvind_patil — Fri, 17 Nov 2017 11:55:37 GMT

Tried this one also no luck buddy.

I am also ebullient to understand the reason.

Thanks,

Arvind Patil