Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
MVP
MVP

Identify Capital Letters

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

9 Replies
rajeshvaswani77
Valued Contributor III

Identify Capital Letters

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

Identify Capital Letters

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.

Identify Capital Letters

Hi,

     Check with this

     Load

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

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

     From

          Datasource

Celambarasan

rajeshvaswani77
Valued Contributor III

Identify Capital Letters

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

Identify Capital Letters

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

if(upper(name) = name,

rajeshvaswani77
Valued Contributor III

Identify Capital Letters

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

-RV

Not applicable

Identify Capital Letters

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

Identify Capital Letters

yup

MVP
MVP

Identify Capital Letters

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

Community Browser