topic Re: Trim Function Question. in QlikView

Trim Function Question.

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

Hi,

I want show only the first two letters in my field. how to achieve this?

Ex:

Field:

ABAS

SDFW

1244S

SDFG

SD646

545SD

SDE12

124563

12448L

SDFGL

145877

I need only the first two letter's not numbers.

Expecting Result:

Field:

Need help!

Re: Trim Function Question.

Peter_Cammaert — Thu, 09 Feb 2017 12:42:01 GMT

left(FieldName, 2)

Is this to be shown in a listbox? I guess not. Where do you want this to happen?

Re: Trim Function Question.

jayanttibhe — Thu, 09 Feb 2017 12:46:58 GMT

Use following :

PurgeChar(Left([Field:],2), '1234567890')

Re: Trim Function Question.

sunny_talwar — Thu, 09 Feb 2017 12:49:37 GMT

May be this:

LOAD Field,

If(IsText(Left(Field, 2)), Left(Field, 2)) as NewField

FROM ....

New field will only have two letters where they are text and not number. But it will include a value like 1A or B3

Re: Trim Function Question.

Anil_Babu_Samineni — Thu, 09 Feb 2017 12:53:45 GMT

Sunny, How about if values are like below

Field:

ABAS

SDFW

1244S

SDFG

SD646

545SD

SDE12

124563

12448L

SDFGL

145877

A123

May be this?

If(Index(Field,'AB'),'AB', If(Index(Field,'SD'),'SD')) as NewField

Re: Trim Function Question.

sunny_talwar — Thu, 09 Feb 2017 12:55:14 GMT

Like I said, my way would include A1.... but the issue with your code is that it won't include CD7264 for example

Re: Trim Function Question.

jayanttibhe — Thu, 09 Feb 2017 12:58:10 GMT

Well he wanted first 2 letters and not numbers: The first 2 letters may be anything . AB OR SD are just examples I think. His requirement is bit unclear - I guess he dont want anything if there is any numeric value .

I have taken care of it in my expression.

Re: Trim Function Question.

MuraliPrasath — Thu, 09 Feb 2017 13:12:47 GMT

Thank you 🙂 It Worked.

Re: Trim Function Question.

Peter_Cammaert — Thu, 09 Feb 2017 13:13:27 GMT

But your solution isn't correct either. You would end up with 6 times an SD value, while Murali only lists 5 of them.

I think the expression will be a bit more complex. Something along the lines of

IF (Len(Left(PurgeChar(Field, '1234567890'), 2) = 2,

Left(PurgeChar(Field, '1234567890'), 2))

And then we all hope for a QlikView expression optimiser, don't we?

Re: Trim Function Question.

Peter_Cammaert — Thu, 09 Feb 2017 13:15:39 GMT

OK.

Please post correct requirements next time.

Re: Trim Function Question.

jayanttibhe — Thu, 09 Feb 2017 13:29:18 GMT

I think if that's the case then we would have done : Result from your Expression would have achieved easily by

Left(PurgeChar([Field:], '1234567890'), 2)

No need to calculate Length of the field and add IF condition

Re: Trim Function Question.

Peter_Cammaert — Thu, 09 Feb 2017 13:35:50 GMT

True, I messed up as well

My original unposted solution reversed the calls, and that one would be the more correct one. That is, if requirements hadn't changed...

IF (Len(PurgeChar(Left(Field, 2), '1234567890')) = 2,

Left(Field, 2))

produces

Re: Trim Function Question.

sunny_talwar — Thu, 09 Feb 2017 13:38:04 GMT

What happens if the Field is A1H293... Is the output needed A1 or AH or A? I guess we lack the clarity as to what was needed here