Identify Capital Letters

[#1]

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?

[rajeshvaswani77]

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

[#3]

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]

Hi,

     Check with this

     Load

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

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

     From

          Datasource

Celambarasan

[rajeshvaswani77]

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

[#6]

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

if(upper(name) = name,

[rajeshvaswani77]

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

-RV

[#8]

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]

yup

[swuehl · Accepted solution]

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