topic Re: Leading Zeros and Num(Field) Function in QlikView

Leading Zeros and Num(Field) Function

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

Hi Commuity,

To remove Leading zeros in following field we can use Num(Field) but it creates null for value of CGDF, GHJJJJ etc

Can we have any workaround solution at script level to keep the CGDF,GHJJJJ etc as it is and only remove those leading zeros

Field
0000154
005
CGFD
GHJJJJ
01

Output should be


154
5
CGFD
GHJJJJ
1

Re: Leading Zeros and Num(Field) Function

Anil_Babu_Samineni — Tue, 24 Jan 2017 07:00:52 GMT

May be using Keepchar / Purgechar

PurgeChar(Field, '0')

Re: Leading Zeros and Num(Field) Function

surendraj — Tue, 24 Jan 2017 07:08:06 GMT

May be like this

Trim(replace(Feild,'0',' '))

replace(Feild,'0','')

Re: Leading Zeros and Num(Field) Function

sspawar88 — Tue, 24 Jan 2017 07:11:01 GMT

if Field has a value like 000150 it will create 15.

i think it is not a perfect one here. in this case we want 150 (only removing leading zeros)

Re: Leading Zeros and Num(Field) Function

Anil_Babu_Samineni — Tue, 24 Jan 2017 07:13:24 GMT

Can you send real data. We can work for you if you provide 10-15 Records

Re: Leading Zeros and Num(Field) Function

surendraj — Tue, 24 Jan 2017 07:14:56 GMT

In such case

replace(ltrim(Replace('000150','0',' ')),' ',0)

replace(ltrim(Replace(Field,'0',' ')),' ',0)

Re: Leading Zeros and Num(Field) Function

MK_QSL — Tue, 24 Jan 2017 07:16:06 GMT

Data:

Load

Field as OriginalField,

Right(Field,Len(Field)-FindOneOf(Upper(Field),'ABCDEFGHIJKLMNOPQRSTUVWXYA123456789')+1) as NEW

Inline

[

Field

0000154

005

CGFD

GHJJJJ

];

Re: Leading Zeros and Num(Field) Function

sspawar88 — Tue, 24 Jan 2017 07:19:02 GMT

Anilbabu,

Values are just like original data. You can work on that

Re: Leading Zeros and Num(Field) Function

Anil_Babu_Samineni — Tue, 24 Jan 2017 07:21:32 GMT

Try this

LET Var1 = 1;

Right(Field,Len(Field)-FindOneOf(Upper(Field),'ABCDEFGHIJKLMNOPQRSTUVWXYA123456789')+'$(Var1)')

Update: Manish gave same check that and let us know

Re: Leading Zeros and Num(Field) Function

Anonymous — Tue, 24 Jan 2017 08:11:52 GMT

Try this

Replace(Field,0,'') as FieldN