Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
felix18807
New Contributor III

Removing Leading Zeros in a comma separated List

Okay so if you want to remove leading zeros from a field value it's fairly straightforward if you search the forums there are a couple of solutions available such as-

Num(FieldName)

replace(ltrim(replace(MyField,'0',' ')),' ','0')

but say you want to remove leading zeros numbers in a comma separated list.

So...

123456789,0123456789,0000444444

Would be transformed into

123456789,123456789,444444

The reason I'm asking is that I have a qlikview document that allows a user to paste comma separated lists into a variable input box and it searches normalised (read leading zeros removed) Id field by producing a string like (123456789|0123456789|0000444444). I need to get it to search for (123456789|123456789|444444) instead. Is there an easy way of achieving this?

Many Thanks

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Removing Leading Zeros in a comma separated List

vString: 1234567809,01234567890,0000444444

Search string: ='(' &concat( num( subfield(vString,',',ValueLoop(1,1+SubStringCount(vString,','))) ) ,'|') & ')'


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Not applicable

Re: Removing Leading Zeros in a comma separated List

How about purgechar( Field,'0')?

felix18807
New Contributor III

Re: Removing Leading Zeros in a comma separated List

Its good but it would cause problems in values like 001234560458 being transformed into 123456458 rather than the desired 1234560458..

MVP & Luminary
MVP & Luminary

Re: Removing Leading Zeros in a comma separated List

vString: 1234567809,01234567890,0000444444

Search string: ='(' &concat( num( subfield(vString,',',ValueLoop(1,1+SubStringCount(vString,','))) ) ,'|') & ')'


talk is cheap, supply exceeds demand

View solution in original post

felix18807
New Contributor III

Re: Removing Leading Zeros in a comma separated List

That works incredibly! Will need to spend some time working out how but a brilliant response.

Many Thanks

Felix