Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Coverting lastname, firstname to firstname lastname

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

1 Solution

Accepted Solutions
Nicole-Smith

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

View solution in original post

9 Replies
Nicole-Smith

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

View solution in original post

Not applicable
Author

I would use this.

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

Thanks

AJ

Not applicable
Author

Greatly appreciate your answers they worked

Nicole-Smith

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

Not applicable
Author

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)

Not applicable
Author


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.

Not applicable
Author

That helps !!!