Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
purgechar(Field1,0123456789) as Field1
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 ...
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 -> ?
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
Thanks Kaushik for quick reply. I too got one solution. I'm attaching my solution as a reply to the original post.
Regards,
Joy
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
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
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.