Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Trim,PuregChar or Subfield

Hi

I have some Data as follows:

X123

A09X

K85X

A12 D

B56 A

I am trying to tidy the data to resemble

X123

A09

K85

A12

B56

By using PurgChar it removes all The X,D & A's which is not my desired result.

Can anyone suggest a successful way of doing this?

Thanks

6 Replies
marcarreras
Valued Contributor

Re: Trim,PuregChar or Subfield

Hi,

It depends on the rule you are applying.

If you have a concrete small number of conversions, i would do by if statement.

If you are able to think in a concrete rule, then let's see...

Otherwise you can load a conversion table with input and output fields, and join it after load your data.

Marc.

MVP
MVP

Re: Trim,PuregChar or Subfield

Left(Field,1) & PurgeChar(Trim(SubField(Field,' ',1)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as NewField

Re: Trim,PuregChar or Subfield

HI

Try like this

Load *, If(IsNum(Right(Field, 1)), Field, Trim(Mid(Field, 1, Len(Field)-1))) as Result Inline

[

Field

X123

A09X

K85X

A12 D

B56 A

];

Re: Trim,PuregChar or Subfield

Hi,

Try like

Temp:

LOAD * INLINE [
TempData
X123
A09X
K85X
A12 D
B56 A
]
;

load TempData,
right(TempData,1) as testTemp,
if(WildMatch(right(TempData,1),'1','2','3','4','5','6','7','8','9','0'),TempData,Mid(TempData,1,LEN(TempData)-1)) as new_data
resident Temp;

DROP Table Temp;

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

Re: Trim,PuregChar or Subfield

Thanks All.

I have ended up with a function within the extracting database to trim off the various characters before extraction. All you help was much appreciated.

Thank You

Re: Trim,PuregChar or Subfield

You want to remove the non-numerics from the right side, correct? I think Manish was on the right track but I would code it as

=left(Field,1) & KeepChar(mid(Field,2),'0123456789')

-Rob

Community Browser