Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

agf
New Contributor II

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
Honored Contributor III

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

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

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

4 Replies
omarbensalem
Esteemed Contributor

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

do all the fields have the . ?

Capture.PNG

Digvijay_Singh
Honored Contributor III

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

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

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

omarbensalem
Esteemed Contributor

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

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

agf
New Contributor II

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

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