topic Re: Complex Character Trimming in QlikView

Complex Character Trimming

Fri, 13 Mar 2015 14:56:11 GMT

Hi Everyone,

As part of my current work - I had to cross reference a selection of part numbers for an application I'm building. While these numbers are correct - the part numbers in the third column here have extra characters tagged on at the end as the picture attached below shows:

What I would like to do here is remove the characters for part numbers in the 15-16 Levels field that begin with '-P' as this is not the required format for the data to be displayed. Can anyone help to provide an effective way of doing this?

Thanks,

Leigh

Re: Complex Character Trimming

Fri, 13 Mar 2015 15:06:44 GMT

Hi Leigh,

Subfield([15-16 Levels],'-P',1) AS New Field

will just give you the first portion of the field before the '-P'

hope that helps

Joe

Re: Complex Character Trimming

Fri, 13 Mar 2015 15:14:35 GMT

Hi Joe,

Thanks for that.

Worked like a charm!

Thanks,

Leigh

Re: Complex Character Trimming

Fri, 13 Mar 2015 15:22:59 GMT

No problem glad to help.

That only really work if the field value is always -pxxx

if there is potential for that to differ, something like the below might work better.

left( KeepChar([15-16 Levels],'0123456789-') ,index(KeepChar([15-16 Levels],'0123456789-') ,'-',-1) -1)

Joe

Re: Complex Character Trimming

vishsaggi — Fri, 13 Mar 2015 15:32:54 GMT

Hi,

As Simmons mentioned Subfield which functions stunningly superb.

The below would give you as you required. Provided if the right end of value have only 3 chars after hyphen '-'

left([15-16 Levels], Len([15-16 Levels])-4)

Cheers,
Vish.