Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pranaview
Creator III
Creator III

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
sunny_talwar

One solution could be

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

View solution in original post

14 Replies
dplr-rn
Partner - Master III
Partner - Master III

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
Creator III
Creator III

or Maybe like this :

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

amit_gupta
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.

jayanttibhe
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"

amit_gupta
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 .


sunny_talwar

One solution could be

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

vishsaggi
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

pranaview
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Thanks everyone for the suggestions. I appreciate it.