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

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

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

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

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

dplr-rn — Tue, 09 Oct 2018 15:52:30 GMT

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

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

jayanttibhe — Tue, 09 Oct 2018 16:07:25 GMT

or Maybe like this :

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

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

amit_gupta — Tue, 09 Oct 2018 16:11:40 GMT

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.

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

jayanttibhe — Tue, 09 Oct 2018 16:18:39 GMT

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"

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

amit_gupta — Tue, 09 Oct 2018 17:07:20 GMT

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 .

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

sunny_talwar — Tue, 09 Oct 2018 17:25:20 GMT

One solution could be

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

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

vishsaggi — Tue, 09 Oct 2018 19:37:27 GMT

May be another way can be :

Write this as an expr or in load script.

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

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

pranaview — Thu, 11 Oct 2018 13:57:36 GMT

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

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

pranaview — Thu, 11 Oct 2018 14:01:54 GMT

Thanks everyone for the suggestions. I appreciate it.

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

vishsaggi — Thu, 11 Oct 2018 14:18:15 GMT

Did you try my expression without the letter G?

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

pranaview — Mon, 15 Oct 2018 08:33:47 GMT

Yes I tried the expression without G and it doesn't work in this scenario as it will remove the first character regardless of what it is. The order format for my field starts with G or sometimes another letter but for now i'm only concerned about the order numbers starting with the G. But if in future the requirement differs then i'll have to make adjustments to this expression. Like maybe putting a condition to check if first character of the order number is a digit or not.

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

vishsaggi — Mon, 15 Oct 2018 13:59:45 GMT

Can you show some eg' values where this did not work? Just wanted to check.

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

pranaview — Tue, 16 Oct 2018 13:42:19 GMT

If suppose the Order number is 8308510406 then after going through this expression, you will get 308510406. as output.

So it removes the first character irrespective of whether it is a G or a digit.

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

vishsaggi — Tue, 16 Oct 2018 14:08:00 GMT

Strange this is what i see when i use my expr using the eg you gave.