topic Re: using string function ..index,left,mid etc in QlikView

using string function ..index,left,mid etc

Anonymous — Thu, 14 Jan 2016 17:02:30 GMT

I have a column with name like

John_Doe

Mary Jane

Charles,Ray

I want to break them into first and lastname

Re: using string function ..index,left,mid etc

Gysbert_Wassenaar — Thu, 14 Jan 2016 17:08:14 GMT

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

Re: using string function ..index,left,mid etc

Anonymous — Thu, 14 Jan 2016 17:27:42 GMT

Thank you for the quick reply

But it gives me error,may be I missed something

I'm attaching a sample file here

Re: using string function ..index,left,mid etc

sunny_talwar — Thu, 14 Jan 2016 17:32:11 GMT

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"

];

Re: using string function ..index,left,mid etc

sunny_talwar — Thu, 14 Jan 2016 17:35:04 GMT

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"

];

Re: using string function ..index,left,mid etc

Anonymous — Thu, 14 Jan 2016 17:38:21 GMT

Thank you Sunny !

Re: using string function ..index,left,mid etc

Kushal_Chawda — Thu, 14 Jan 2016 17:44:08 GMT

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"

];

Re: using string function ..index,left,mid etc

Anonymous — Thu, 14 Jan 2016 18:00:28 GMT

Sunny,

Do you maintain a blog ?

If no you should write one.

Regards

Re: using string function ..index,left,mid etc

sunny_talwar — Thu, 14 Jan 2016 18:05:37 GMT

Hahaha thanks Balraj‌‌ for seeing the potential in me .

I don't have a blog, but its on radar.

Best,

Sunny