Coverting lastname, firstname to firstname lastname

[#1]

Hi all,

I am trying to convert lastname, firstname to firstname lastname to join two tables, I am new to qlikview I am not aware of what functions to use, some of the functions I was using in SQL are not supported here. The code I am trying to run is

select substring(name, charindex(',', replace(name, ' ', '')) + 1, len(name))

  + ' '

  + left(name, charindex(',', name) -1) as Name

from table

This doesnt seem to work in qlikview as it doesnt support substring and charindex Please provide me some inputs on what functions I can use and What can I do differently on the existing code

Appreciate your help

[Nicole-Smith · Accepted solution]

trim(subfield(YourNameField, ',', 2) & ' ' & subfield(YourNameField, ',', 1))

[#3]

I would use this.

SubField(Name,' ',2)&' '&SubField(Name,' ',1)  as  Revname

Thanks

AJ

[#4]

Greatly appreciate your answers they worked

[Nicole-Smith]

Please mark answers as helpful and correct so others can find solutions to their problems too

[#6]

Hi,

Some od the rows contain firstname lastname format some of them contain last name, first name  last name

do I need to use if condition in such case please provide me with some input .

The way I am thinking on doing is select (case when Name like '%,%'

but I do not find case and like functions

Appreciate your help on this

[Nicole-Smith]

if(YourNameField like '*,*',

     trim(subfield(YourNameField, ',', 2) & ' ' & subfield(YourNameField, ',', 1)),

          YourNameField)

[#8]

Hi Nicole,

I am also observing different patterns in the name format, one of my tables contains name with filed with FN LN, LN FN, LN, FN formats these three diffreenvt formats should be converted to one format FN LN to join with other table Please provide me some inputs

Appreciate your help on this

[Nicole-Smith]

LN,FN and FN LN are easy to distinguish because one has a comma whereas the other one does not.  However, we cannot distinguish between FN LN and LN FN since the formats are exactly the same.  I think you'll need to fix the actual data in order to get this one to work.

[#10]

That helps !!!