How to remove specific characters from Left for all the values of a field in QlikView

[pranaview]

Hi Guys,

I have a field which I require for mapping purpose. But it is adding G00/G0/G prefix to all the order numbers that I want to match.

For Example : G0080202594 or G0285144563 or G1085346991

So, what I want is to trim off the initial G or any 0 afterwards which will give me something like this 80202594/285144563 /1085346991.

As I can't tell for sure how many 0s will be there after G letter so I can't just remove a specific number of letters from beginning. I would like to know how can I remove the G and any 0 that comes right after(if any).

Any help will be appreciated.Thanks in advance.

Pranav

[dplr-rn]

Below should cover your scenarios mentioned in your post. Just remove G and convert to a number

Num(Replace('G0080202594','G',''))

Hope it works out

[jayanttibhe]

or Maybe like this :

Num(mid('G0080202594',2,len('G0080202594')))

[amit_gupta]

Hi Pranav,

Try this:

PurgeChar(text, remove_chars)

Example Result:

PurgeChar ( 'a1b2c3','123' ) Returns 'abc'

PurgeChar ( 'a1b2c3','312' ) Returns 'abc'

In your Case:

PurgeChar ( 'G0080202594','G0')

It will result you --> 822594

Note -- It will remove all occurrence of G and 0.

I hope, it will help you.

[jayanttibhe]

Hi Amit,

Purging is not the solution. It will remove the Valid "0" from the Fieldvalue. i.e. 80202594 -- >   822594. this is not what he want. He just want to remove the Leading "0" and the characther "G"

[amit_gupta]

Hi Jayant,

I think in that case, We can directly use

Num(PurgeChar ('G0080202594','G') ) instead of PurgeChar ( 'G0080202594','G0')

It will remove G and Num will removes all initial 0's, as Zero''s before any non zero numbers automatically will be removed, when we use Num().

I think, it will work .

[sunny_talwar · Accepted solution]

One solution could be

Replace(LTrim(Replace(Mid([Order Number], 2, Len([Order Number])-1), '0', ' ')), ' ', '0')

[vishsaggi]

May be another way can be :

Write this as an expr or in load script.

= Mid(TextStr, FindOneOf(TextStr, '123456789'), Len(TextStr)) AS NewStr

[pranaview]

Hi Sunny,

Thanks for the solution. Works for my current requirement but if in future I get order numbers without G then this expression will trim off the first digit as well. So i think i will have to put a condition to check if first character is number or a letter and then do the needful. But this solution should do it for now. Again thank you so much for the help.

Pranav

[pranaview]

Thanks everyone for the suggestions. I appreciate it.