topic Re: How to conditionally remove last Char from a text field ? in QlikView

How to conditionally remove last Char from a text field ?

Thu, 15 Nov 2012 14:15:09 GMT

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

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 — Thu, 15 Nov 2012 14:25:57 GMT

Try like this

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

jagan — Thu, 15 Nov 2012 16:10:49 GMT

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.

Thu, 15 Nov 2012 16:23:54 GMT

Hi Mayil

Very ellegant solution worked fine

Thu, 15 Nov 2012 16:26:33 GMT

Thanks Jagan

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