topic Re: How to get name in right order in QlikView

How to get name in right order

Wed, 17 Jul 2013 14:31:55 GMT

Hi all,

I have a field called NameVZ which looks like this : F.M.J. Hof van den

where F.M.J. are the initials, Hof is the surname and "van den" the preposition.

I'd like to create two fields out of this. One with the Initials which is easy -> SubField(NameVZ, ' ',1) as Initials

The other field should have preposition + Surname in one: "van den Hof"

But how do I do that as the propostion can be different for each name?

So to be clearer:

Field NameVZ (examples)

F.M.J. Hof van den

T. Johnson

S. Haaren van

B. Beuker de

should become

Field FullnameVZ

van den Hof

Johnson

van Haaren

de Beuker

Preposition before the surname

Re: How to get name in right order

Anonymous — Wed, 17 Jul 2013 14:37:19 GMT

There are many ways. Try this for example

=textbetween(NameVZ, ' ', '')

Re: How to get name in right order

Anonymous — Wed, 17 Jul 2013 14:47:24 GMT

You could do a right() on the name, using the width of the intials that you've already detected.

Something like this:

right(NameVZ,Len(NameVZ)-SubField(NameVZ, ' ',1))

Jonathan

Re: How to get name in right order

Wed, 17 Jul 2013 14:51:13 GMT

Hi Jonathan,

Don't think that works as this gives me "Hof van den" as where I would like to have "van den Hof"

Re: How to get name in right order

Wed, 17 Jul 2013 14:53:30 GMT

Hi Michael,

Can you clarify this a bit, as I don't understand how this works in my situation.

Re: How to get name in right order

Anonymous — Wed, 17 Jul 2013 14:58:25 GMT

The textbetween(text, s1,s2) returns substring of the "text" field value between text s1 and text s2. In your situation, I used s1 = ' ' (one space), and s2='' (nothing). So, the result wil be from the character after the first space and to the end.

Regards,

Michael

Re: How to get name in right order

Anonymous — Wed, 17 Jul 2013 15:03:29 GMT

Another option is to use mid():

=mid(NameVZ, index(NameVZ, ' ')+1)

Here I calculate the position of the 1st space, add 1 to get to the position of the first character after space, and use mid() to cut from that position to the end.

Re: How to get name in right order

Anonymous — Wed, 17 Jul 2013 16:13:14 GMT

Sounds like you want to set up a specific processing rule should you find the existence of "van den", and that this is at the very end of the string.

In that case, something like this may do it:

SET EndName = textbetween(NameVZ, ' ', '');

if(Lower(Right(NameVZ,7)='van den',

'Van den' & textbetween(EndName, ' ', ''),

textbetween(NameVZ, ' ', '')

)

I think I prefer Michael's texttbetween() function to the right(), mid() etc string chopping functions. Guess it's the thing I've learnt from the forum today.

Jonathan

Re: How to get name in right order

CELAMBARASAN — Wed, 17 Jul 2013 18:05:08 GMT

Hi,

You can make use of it

LOAD *, Trim(Mid(Trim(Mid(Text, Len(Initial)+1)), Index(Trim(Mid(Text, Len(Initial)+1)), ' ')) & ' ' & SubField(Trim(Mid(Text, Len(Initial)+1)),' ', 1)) AS Name;

LOAD *, Left(Text, index(Text, '.', -1)) AS Initial Inline [

Text

F.M.J. Hof van den

G.N.K. Ipg wbo efo

H.O.L. mqh xap fgp];

Re: How to get name in right order

Thu, 18 Jul 2013 07:42:06 GMT

No, "van den" is just a possibility. In other names it can be blank or have other options like "van" "van de" "de" "den" etc..

So searching on text is no option.

Re: How to get name in right order

Thu, 18 Jul 2013 08:41:23 GMT

Thx Michael,

Your suggestion helped me on the right track.

This is what I've used now:

If(len(Trim(TextBetween(TextBetween(NaamVZ, ' ',''),' ','')))= 0, SubField(NaamVZ, ' ', 2), TextBetween(TextBetween(NaamVZ, ' ',''),' ','') & ' ' & SubField(NaamVZ, ' ', 2)) as AchternaamVZ

It's not 100% waterproof (e.g. da Silva de Jesus - > Silva de Jesus Da) but it will do.

Re: How to get name in right order

Anonymous — Thu, 18 Jul 2013 12:05:12 GMT

How about looping through a set of known name prepositions then. Something like this:

SET vName ='';

SET vEndName = textbetween(NameVZ, ' ', '');

FOR EACH vPrep IN ' van den',' van der',' van de',' de',' den'

if Lower(Right(vEndName,Len(vPrep))=vPrep THEN

SET vName = vPrep & textbetween(vEndName, ' ', ''),

EXIT FOR

ENDIF

IF Len(vName)=0 THEN

SET vName =vEndName

ENDIF

Note:

1. the spaces at the start of each of the listed vPrep options.

2. the 'van ..' options should go before those without 'van ..'. The EXIT FOR command should then prevent 'der' being used before 'van der'

Jonathan

Re: How to get name in right order

Thu, 18 Jul 2013 12:08:13 GMT

Thx for your effort Jonathan, but Michael already got me on the right track.

See above for the solution I used.