Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
trim(subfield(YourNameField, ',', 2) & ' ' & subfield(YourNameField, ',', 1))
trim(subfield(YourNameField, ',', 2) & ' ' & subfield(YourNameField, ',', 1))
I would use this.
SubField(Name,' ',2)&' '&SubField(Name,' ',1) as Revname
Thanks
AJ
Greatly appreciate your answers they worked
Please mark answers as helpful and correct so others can find solutions to their problems too
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
if(YourNameField like '*,*',
trim(subfield(YourNameField, ',', 2) & ' ' & subfield(YourNameField, ',', 1)),
YourNameField)
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
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.
That helps !!!