Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

remove the fourth character from the end in a string

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.

1 Solution

Accepted Solutions
MVP
MVP

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

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

4 Replies

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

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

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

MVP
MVP

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

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

Not applicable

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

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.

Not applicable

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

a:

LOAD * INLINE [

    F1

    123456******7890

];

load    

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

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

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

       

        Resident a;