topic Identify Capital Letters in QlikView

Identify Capital Letters

Tue, 21 Feb 2012 16:56:43 GMT

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?

Identify Capital Letters

rajeshvaswani77 — Tue, 21 Feb 2012 17:12:22 GMT

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

Identify Capital Letters

Tue, 21 Feb 2012 17:16:39 GMT

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

CELAMBARASAN — Tue, 21 Feb 2012 17:20:23 GMT

Hi,

Check with this

Load

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

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

From

Datasource

Celambarasan

Identify Capital Letters

rajeshvaswani77 — Tue, 21 Feb 2012 17:24:17 GMT

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

Identify Capital Letters

Tue, 21 Feb 2012 17:31:52 GMT

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

if(upper(name) = name,

Identify Capital Letters

rajeshvaswani77 — Tue, 21 Feb 2012 17:42:02 GMT

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

-RV

Identify Capital Letters

Tue, 21 Feb 2012 17:47:14 GMT

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

Identify Capital Letters

rajeshvaswani77 — Tue, 21 Feb 2012 17:47:52 GMT

yup

Identify Capital Letters

swuehl — Tue, 21 Feb 2012 17:48:30 GMT

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