Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a column with name like
John_Doe
Mary Jane
Charles,Ray
I want to break them into first and lastname
Or even this would work:
Test:
LOAD Pick(Match(KeepChar(Name, '_, '), ',', '_', ' '), SubField(Name, ',', 2), SubField(Name, '_', 1), SubField(Name, ' ', 1)) as [First Name],
Pick(Match(KeepChar(Name, '_, '), ',', '_', ' '), SubField(Name, ',', 1), SubField(Name, '_', 2), SubField(Name, ' ', 2)) as [Last Name];
Load *
INLINE [
Name
John_Doe
Mary Jane
"Charles,Ray"
];
Perhaps like this:
If(index(Name,',',subfield(Name, ',',-1),
if(index(Name,'_',subfield(Name,'_',1),
if(index(Name,' ',subfield(Name,' ',1))) as FirstName
If(index(Name,',',subfield(Name, ',',1),
if(index(Name,'_',subfield(Name,'_',-1),
if(index(Name,' ',subfield(Name,' ',-1))) as LastName
Thank you for the quick reply
But it gives me error,may be I missed something
I'm attaching a sample file here
Try this may be:
Test:
LOAD If(KeepChar(Name, '_, ') = ',', SubField(Name, ',', 2),
If(KeepChar(Name, '_, ') = '_', SubField(Name, '_', 1),
If(KeepChar(Name, '_, ') = ' ', SubField(Name, ' ', 1)))) as [First Name],
If(KeepChar(Name, '_, ') = ',', SubField(Name, ',', 1),
If(KeepChar(Name, '_, ') = '_', SubField(Name, '_', 2),
If(KeepChar(Name, '_, ') = ' ', SubField(Name, ' ', 2)))) as [Last Name];
Load *
INLINE [
Name
John_Doe
Mary Jane
"Charles,Ray"
];
Or even this would work:
Test:
LOAD Pick(Match(KeepChar(Name, '_, '), ',', '_', ' '), SubField(Name, ',', 2), SubField(Name, '_', 1), SubField(Name, ' ', 1)) as [First Name],
Pick(Match(KeepChar(Name, '_, '), ',', '_', ' '), SubField(Name, ',', 1), SubField(Name, '_', 2), SubField(Name, ' ', 2)) as [Last Name];
Load *
INLINE [
Name
John_Doe
Mary Jane
"Charles,Ray"
];
Thank you Sunny !
another way to do is like below
Test:
Load *, subfield(replace(replace(Replace(Name,'_','*'),' ','*'),',','*'),'*',1) as [First Name],
subfield(replace(replace(Replace(Name,'_','*'),' ','*'),',','*'),'*',2) as [Last Name]
INLINE [
Name
John_Doe
Mary Jane
"Charles,Ray"
];
Sunny,
Do you maintain a blog ?
If no you should write one.
Regards
B
Hahaha thanks Balraj for seeing the potential in me .
I don't have a blog, but its on radar.
Best,
Sunny