Removing trailing numbers from dimension field values

[joydipp1988]

Dear all,

I am facing an issue regarding the data preparation from an excel data. The issue is described below-

Some dimension values have a trailing nos. which should not be there. E.g. under Field1 we have several values with trailing nos. See the snap below.

Field1

ABC

ABC1

ABC2

ABC3

ABC4

I want to remove all the unwanted trailing nos. for Field1 and Field2. Please find the attach data file. Also side by side I showed the desired output. Can anyone please help me in achieving the result ? Any help will be appreciated. Tanks in advance.

Regards,

Joy

[kaushiknsolanki · Accepted solution]

Hi,

Try below script.

Load if(IsNum( Right(Field1,1)),Left(Field1,Len(Field1)-1),Field1) as Field2,Field1 inline [

Field1

ABC

ABC1

ABC2

ABC3

ABC4

DEFXY

DEFXY1

DEFXY2

DEFXY3

GHI

GHI1

GHI2

JKLMNO

JKLMNO1

JKLMNO2

JKLMNO3

JKLMNO4

];

Regards,

Kaushik Solanki

[varshavig12]

purgechar(Field1,0123456789) as Field1

[Anonymous]

I agree with Kaushik, however based on the .xlsx file this might be a more appropriate script:

LOAD

     If(IsNum(Right(Field1,1)),Left(Field1,Len(Field1)-1),Field1) as Field1,

     If(IsNum(Right(Field2,1)),Left(Field2,Len(Field2)-1),Field2) as Field2,

     Values

From ...

[MayilVahanan]

Hi

Do you want to remove all the numeric values in field1 and Field2? Or only Trailing numbers

For ex:

ABC1 -> ABC

1ABC -> ?

A1BC -> ?

ABC123 -> ?

[joydipp1988]

Hi Varsha,

Thanks for the reply. But in real case,my scenario a bit different that's why can't use Purgechar(). See the snap below-

See I want to remove blue portion nos only, but not the yellow portion nos. Purgechar() will remove all the nos. That's why can't use Purgechar().

Regards,

Joy

[joydipp1988]

Thanks Kaushik for quick reply. I too got one solution. I'm attaching my solution as a reply to the original post.

Regards,

Joy

[joydipp1988]

Hi Mayil,

I want to remove trailing nos only. Thanks for your interest. Also I got one solution.

I'm attaching my solution as a reply to the original post.

Regards,

Joy

[joydipp1988]

Hello All,

Thanks for the help. I was trying to solve it too and got the solution. Here is my script code-

DATA:

LOAD

  If( Match(Right(Field1,1),1,2,3,4,5,6,7,8,9,0), Replace(Field1, Right(Field1,1),''), Field1) as Field1,

  If( Match(Right(Field2,1),1,2,3,4,5,6,7,8,9,0), Replace(Field2, Right(Field2,1),''), Field2) as Field2

  Values

FROM

[Test Data.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Data);

Regards,

Joy

[varshavig12]

ooh, I just worked on the sample data provided.

I should have paid attention on the word trailing.

Anyway glad, you got so many ways to get the desired output.