topic Re: Removing trailing numbers from dimension field values in QlikView

Removing trailing numbers from dimension field values

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

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

Re: Removing trailing numbers from dimension field values

kaushiknsolanki — Tue, 26 Jul 2016 08:53:48 GMT

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

Re: Removing trailing numbers from dimension field values

varshavig12 — Tue, 26 Jul 2016 09:00:54 GMT

purgechar(Field1,0123456789) as Field1

Re: Removing trailing numbers from dimension field values

Anonymous — Tue, 26 Jul 2016 09:05:55 GMT

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 ...

Re: Removing trailing numbers from dimension field values

MayilVahanan — Tue, 26 Jul 2016 09:09:49 GMT

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

For ex:

ABC1 -> ABC

1ABC -> ?

A1BC -> ?

ABC123 -> ?

Re: Removing trailing numbers from dimension field values

joydipp1988 — Tue, 26 Jul 2016 09:10:57 GMT

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

Re: Removing trailing numbers from dimension field values

joydipp1988 — Tue, 26 Jul 2016 09:16:24 GMT

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

Regards,

Joy

Re: Removing trailing numbers from dimension field values

joydipp1988 — Tue, 26 Jul 2016 09:18:42 GMT

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

Re: Removing trailing numbers from dimension field values

joydipp1988 — Tue, 26 Jul 2016 09:23:23 GMT

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

Re: Removing trailing numbers from dimension field values

varshavig12 — Tue, 26 Jul 2016 12:02:26 GMT

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.