topic Re: remove the fourth character from the end in a string in QlikView

remove the fourth character from the end in a string

Tue, 12 Feb 2013 07:57:38 GMT

Hi there,

I have a field that i want to load in my tables.

It is like "123456******7890".

I want to create a new field be removing one of the asterisks

and another by removing the first digit after the asterisks ( in this example the digit 7).

Thank you.

alexandros17 — Tue, 12 Feb 2013 08:10:12 GMT

If your string has a variable lenght and the field name is for example myField you can use

=mid(myField,1,len(myField)-4)

swuehl — Tue, 12 Feb 2013 08:37:21 GMT

Look into the QV string functions section in the HELP file.

Your two expressions for the new fields might look like

LOAD

Number,

replace(Number,'******','*****') as NewNumber1,

left(Number,len(Number)-4)&right(Number,3) as NewNumber2,

...

Depending on the format of the Number field, you might also want to look into index(),findoneof() etc.

Regards,

Stefan

Tue, 12 Feb 2013 08:39:35 GMT

Hi there, thank you for your reply.

The field has a fixed length of 16 digits. Using the code that you gave me it completely removes the last four digits.

I just want yo remove the fourth from the last and keep the last three.

Thank you.

Tue, 12 Feb 2013 08:58:49 GMT

LOAD * INLINE [

123456******7890

];

load

replace(F1,'******','') as Number1,

replace(F1,'7','') as Number11,

left(F1,len(F1)-10) & right(F1,4) as Number2

Resident a;