Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Krzysztof-Wroclawski
Former Employee
Former Employee

Fixing field during LOAD XXXX Name ***NOT***

Hi Experts,

My regular format of the field is 123456 Big Customer Number One I checked Community forum to find the way to remove numbers. Solution which works for me is:

right ("Customer", len ("Customer"]) - 7) AS NameOnly,

However, I have another problem. Some of the Customer's names have ***NOT*** at the end. I would like to exclude number and ***NOT*** part from customer's name.

Summary:

Field   123456 The Biggest Corporation INC. should give The Biggest Corporation INC.

Field    654321 The Biggest Corporation INC. ***NOT*** should also give The Biggest Corporation INC.

Thank you in advance for all tips!

Kris

1 Solution

Accepted Solutions
Digvijay_Singh

You may try with replace() to remove the extra string -

replace(right ("Customer", len ("Customer"]) - 7),'***NOT***','') AS NameOnly,

View solution in original post

4 Replies
OmarBenSalem

do all the fields have the . ?

Capture.PNG

Digvijay_Singh

You may try with replace() to remove the extra string -

replace(right ("Customer", len ("Customer"]) - 7),'***NOT***','') AS NameOnly,

OmarBenSalem

Do as follow:

load if(Index=0,NameOnly1, left(NameOnly1,Index-1)) as NameOnly;

load NameOnly1, index(NameOnly1,'***NOT***') as Index;

load Trim(PurgeChar("Customer",'0123456789')) as NameOnly1 Inline [

Customer

123456 The Biggest Corporation INC.

654321 The Biggest Corporation INC. ***NOT***

];

result:

Capture.PNG

Krzysztof-Wroclawski
Former Employee
Former Employee
Author

Thank you, both! I used Digvijay solution, however, I see Omar's must work as well