Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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

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

1 Solution

Accepted Solutions
Highlighted

One solution could be

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

View solution in original post

14 Replies
Highlighted
Partner
Partner

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

Highlighted
Creator III
Creator III

or Maybe like this :

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

Highlighted
Contributor III
Contributor III

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.

Highlighted
Creator III
Creator III

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"

Highlighted
Contributor III
Contributor III

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 .


Highlighted

One solution could be

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

View solution in original post

Highlighted
Champion III
Champion III

May be another way can be :

Write this as an expr or in load script.

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

Highlighted
Creator II
Creator II

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

Highlighted
Creator II
Creator II

Thanks everyone for the suggestions. I appreciate it.