Discussion Board for collaboration on QlikView Scripting.
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...
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.
Go to Solution.
LOAD * INLINE [
THE MISTER Someone Something
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.
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.
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.
Check with this
Left(FieldName,Index(FieldName,' ')) as LastName,
Right(FieldName,len(FieldName) - Index(FieldName,' ')+1) as FirstName
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
right(string1,<result we got from index>+1, len(string1))
should give the first name.
We are not depending on capitalization here.
The only thing that comes to my mind is an evaluation with upper
if(upper(name) = name,
Differentiating by case is not something good to do. Would not be reliable. Not recommended.
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