How to conditionally remove last Char from a text field ?

[#1]

Hi

I have a data field that as an example contains:-

CB12123A

REQ03456

AT1234B

AT1234A

The A or B in the last Char cannot be guaranteed to always be in the same char position.

What I need to do is strip off the last digit if it is an Alpha Character A or B so I end up with.

CB12123

REQ03456

AT1234

AT1234

I have tried using If Statements with Right and PurgeChar but it does not give desired results.

Any help from the QV community would be welcome

[MayilVahanan · Accepted solution]

HI

Try like this

=if(match(right('AT1234B',1),'A','B'),Left('AT1234B',len('AT1234B')-1))

[jagan]

Hi,

Try this script

LOAD

    Text,

    If(Match(Right(Text, 1),'A','B'), Left(Text, len(Text)-1), Text) AS FormattedText;

LOAD * INLINE [

Text

CB12123A

REQ03456

AT1234B

AT1234A

];

Hope it helps you.

Regards,

jagan.

[#4]

Hi Mayil

Very ellegant solution worked fine

[#5]

Thanks Jagan

I am impressed with the wealth of knowledge there is out there in the Qlikview Community