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: 
Not applicable

Identify Capital Letters

Hi Everyone,

I have surch on the community and with the QlikView Help but I didn't find my answer, maybe you would help me.

I have a filed with full names, so I'm trying to isolate each one of theim.

I already tried two solutions that work for most names:

subfield(Full_Name, ' ',1) for last names or right(Full_Name, len(Full_Name)- FindOneOf(Full_Name, ' ')) for first ones.

But when an "aristocratice name" comes up...

For example:

MISTER Someone => Last Name: MISTER and First Name: Someone

THE MISTER Someone => Last Name: THE and First Name: MISTER Someone.

So I'm trying to identify capital letters so I can split First and Last names.

Any idea?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try this:

NAMES:

LOAD * INLINE [

NAME

THE MISTER Someone Something

];

TMP:

LOAD recno() as recID,if(ord(right(NamePart,1))<97,'UPPER','LOWER') as Case, *;

LOAD NAME, subfield(NAME,' ') as NamePart resident NAMES;

left join (NAMES) LOAD NAME, concat(Capitalize(NamePart),' ', recID) as Firstname Resident TMP where Case ='LOWER' group by NAME;

left join (NAMES) LOAD NAME, concat(Capitalize(NamePart),' ', recID) as LastName Resident TMP where Case ='UPPER' group by NAME;

drop table TMP;

I Capitalized the Last Name to The Mister, but you can just remove the Capitalize() function, if you want to get all in UPPER case THE MISTER.

Regards,

Stefan

View solution in original post

9 Replies
rajeshvaswani77
Specialist III
Specialist III

Could you create a inline table with all possible keywords like MISTER, THE MISTER etc then whenever you find these then escape them. Somthing on these lines. Though I do not have the full solution i could think of right now.

thanks,

Rajesh Vaswani

Not applicable
Author

I was not talking about the status of a person (Mr, Ms, Dr, etc...), my examples wasn't clear.

The last name comes fisrt in captial letter

Then the fisrt name comes with just the first letter in capital

ex: BARACK Obama

And I want to split them up.

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Check with this

     Load

          Left(FieldName,Index(FieldName,' ')) as LastName,

          Right(FieldName,len(FieldName) - Index(FieldName,' ')+1) as FirstName

     From

          Datasource

Celambarasan

rajeshvaswani77
Specialist III
Specialist III

Use string manipulation

index(string1, ' ')

this will give the position of the blank space.

Assuming the blank space is the delimiter here between teh firstname and lastname

next use left(string1,<result we got from index>)

should give the last name

again

right(string1,<result we got from index>+1, len(string1))

should give the first name.

We are not depending on capitalization here.

thanks,

Rajesh Vaswani

Not applicable
Author

The only thing that comes to my mind is an evaluation with upper

if(upper(name) = name,

rajeshvaswani77
Specialist III
Specialist III

Differentiating by case is not something good to do. Would not be reliable. Not recommended.

-RV

Not applicable
Author

But if you see his post, he says that not always first token is last name and the rest is first name.

The only pattern he visualizes is capital letters

rajeshvaswani77
Specialist III
Specialist III

yup

swuehl
MVP
MVP

Try this:

NAMES:

LOAD * INLINE [

NAME

THE MISTER Someone Something

];

TMP:

LOAD recno() as recID,if(ord(right(NamePart,1))<97,'UPPER','LOWER') as Case, *;

LOAD NAME, subfield(NAME,' ') as NamePart resident NAMES;

left join (NAMES) LOAD NAME, concat(Capitalize(NamePart),' ', recID) as Firstname Resident TMP where Case ='LOWER' group by NAME;

left join (NAMES) LOAD NAME, concat(Capitalize(NamePart),' ', recID) as LastName Resident TMP where Case ='UPPER' group by NAME;

drop table TMP;

I Capitalized the Last Name to The Mister, but you can just remove the Capitalize() function, if you want to get all in UPPER case THE MISTER.

Regards,

Stefan