Select name character

[akpofureenughwu]

Hello everyone,

I have a table of staff name I pulled from sql,

First Name	Middle Name	Last Name	Code
John	N/A	Lee	001
Kitty	Brown	Frankie	002
Tracy	Mole	Chuck	003
Grant	N/A	Sam	004

This is the sample of the data.  N/A is there because the staff doesn't have a middlename.

I need to concatenate the First Name, Middle Name and Last Name  to give Full Name

I'm using this expression:

Last_Name&' '&"Middle_Name"& ' '&First_Name as [Full Name]

but if I use the above expression for the staff with code 001.. full name will become John N/A Lee and this is not valid

How do I edit it so I will skip Middle Name when Middle Name  = 'N/A'

[rwunderlich · Accepted solution]

Last_Name&' '& if(Middle_Name<> 'N/A', Middle_Name & ' ', '') & First_Name as [Full Name]

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[sunny_talwar]

May be try this

Last_Name & If(Middle_Name <> 'N/A', ' ' & Middle_Name) & ' ' & First_Name as [Full Name]

[MarcoWedel]

Hi,

another solution could be:

Replace([First Name]&' '&[Middle Name]&' '&[Last Name],' N/A','') as [Full Name]

hope this helps

regards

Marco

[akpofureenughwu]

Thank you Marco. This is great