DSXchange

There is a column called fullname in the source having the name like
"kenneth browne"
where the string before the space is first name and after space is last name


Now my question is,
In the transformer I have two columns called first name and last name
ie kenneth should be populated to first name
and
browne should be populated to last name

I am working ON PX

Could any one throw light on this
Please help me in writing the logic in transformer

you may use

First_Name=Field(fullname,' ', 1)
Last_Name=Field(fullname,' ', 2)

in transformer.

Lets say input column is A

use the following in your derivation or stage variable

Set first Name To The following derivation:

A[1,Index(A,' ',1)]

Set Last Name to the following Derivation:

A[Index(A,' ',1)+1,Len(A)-Index(A,' ',1)]

Hope this helps...

HI I am yet to check with that code as I dont have source data
thanks for the help

Havoc wrote:Lets say input column is A

use the following in your derivation or stage variable

Set first Name To The following derivation:

A[1,Index(A,' ',1)]

Set Last Name to the following Derivation:

A[Index(A,' ',1)+1,Len(A)-Index(A,' ',1)]

Hope this helps...

HI I am yet to check with that code as I dont have source data
thanks for the help

meet_raghu wrote:you may use
First_Name=Field(fullname,' ', 1)
Last_Name=Field(fullname,' ', 2)

in transformer.

karrisuresh wrote:HI I am yet to check with that code as I dont have source data

Why do you need the entire source data? Just plug the "FIELD" function given above in a test function and test it out with a couple of names. You should be done.

karrisuresh wrote:HI I am yet to check with that code as I dont have source data

Why do you need the entire source data? Just plug the "FIELD" function given above in a test function and test it out with a couple of names. You should be done.

Use a Row Generator to generate test data. That's what it's for!

Hi Everyone I have got new problem,
Earlier I thought of first name and last name only
Now I have prob with middle name
For example:
From the source I am getting a name called "kenneth charlie Browne"
in the transformer the name should get split into only two columns called first name and last name
In the first name column "kenneth"
In the last name column"Browne"

Can any one throw soe light on this
I am using Index and field function
but in the last name column I am getting "charlie browne" but I want only the last name"Browne"

thanks for your time

Got new problem? Post new topic!

You would have to use the field function for both first and middle name. Then for the last name use Field(fullname : " ",' ', 3).

first name : Field(fullname,' ', 1)
second name : Field(fullname,' ', 2)
Last name : Field(fullname : " ",' ', 3)

karrisuresh wrote:Hi Everyone I have got new problem,
Earlier I thought of first name and last name only
Now I have prob with middle name
For example:
From the source I am getting a name called "kenneth charlie Browne"
in the transformer the name should get split into only two columns called first name and last name
In the first name column "kenneth"
In the last name column"Browne"

Can any one throw soe light on this
I am using Index and field function
but in the last name column I am getting "charlie browne" but I want only the last name"Browne"

thanks for your time

You can use
First_Name=Field(fullname,' ', 1)
Last_Name=Field(fullname,' ', 3)

Field(fullname,' ', 3) will take the field from 2nd ' ' to 3rd ' ' or till end

thats all..

meet_raghu wrote: You can use
First_Name=Field(fullname,' ', 1)
Last_Name=Field(fullname,' ', 3)

Field(fullname,' ', 3) will take the field from 2nd ' ' to 3rd ' ' or till end

thats all..

That's if there is a middle name. You may have some names with just the first and last names, then some names with first, middle and last names, yet others with first, middle, last and suffixes, and then others with 2 names forming the middle name, or may be even 3. So, your solution has to be generic.

Let me give you a hint -
[Assumption: First name is ALWAYS written first, and last name ALWAYS at the end.]
1. Use COUNT function to count the number of spaces in the name.
2. Use the field function with the first space to get first name.
3. Use the field function with the last space occurrenece (based on Count + 1) to get the last name.

use one stage variable to find out how many spaces are there in Full Name(how many words) using the count function

then you can use Field function accordingly