topic Re: Trim,PuregChar or Subfield in QlikView

Trim,PuregChar or Subfield

Thu, 05 Mar 2015 12:00:55 GMT

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

Re: Trim,PuregChar or Subfield

Anonymous — Thu, 05 Mar 2015 12:05:51 GMT

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.

Re: Trim,PuregChar or Subfield

MK_QSL — Thu, 05 Mar 2015 12:05:57 GMT

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

Re: Trim,PuregChar or Subfield

MayilVahanan — Thu, 05 Mar 2015 12:16:25 GMT

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

PrashantSangle — Thu, 05 Mar 2015 12:28:51 GMT

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

Re: Trim,PuregChar or Subfield

Thu, 05 Mar 2015 14:13:45 GMT

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

rwunderlich — Thu, 05 Mar 2015 16:25:14 GMT

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