Expression - Concat three variables if not empty/null

[MrBosch]

Hi all

This must be a no-brainer but I am stuck.

Fields: 
first name
middle name
last name

I need to concatenate these three but only if there is a value (for example in the middle name).

Output:
John Cleese
Joe John Baker

It will be evaluated in a text object on my dashboard containing more rows:
Full name: John Cleese
Organisation: Baker Inc
etc.

Thanks a lot.

[sidhiq91]

@MrBosch  I not very clear with your requirement, with whatever I have understood I have written a script for you.

My Script will produce full name only if the all the 3 fields i.e. first, middle and last name are not null else it will produce as null.

NoConcatenate
Temp:
Load EmptyIsNull([first name]) as [first name],
EmptyIsNull([middle name]) as [middle name],
EmptyIsNull([last name]) as [last name]

Inline [
first name,middle name,last name
John,,Cleese
Joe, John, Baker
];

NoConcatenate
Temp1:
Load *,
if(isnull([first name]) or isnull([middle name]) or isnull([last name]), null(),
[first name]&' '&[middle name]&' '&[last name]) as Name
Resident Temp;
Drop table Temp;

Exit Script;

Let me know if there is something else that you are looking for.

[MrBosch]

Hi and thanks for your quick reply.

First I tried to avoid the script area. I just wanted to try to get it as an expression to be used in the text object.

Second when I look at your Temp1 output: the John - Cleese should have been John Cleese in the Name column. Now it is empty.

To clarify: I only need to glue variable names together. In my own syntax:

= 'Full name: ' & [first name] & if(notempty ([middle name], [middle name], else '') & [last name] &chr(10) &
'Organization: ' & [organization] etc.

[thomas8927eva]

I have also same issue any suggestion for this so please reply. Thanks in advance.

[marcus_sommer · Accepted solution]

If your fields contain a real NULL you could use coalesce(), like: [First] & coalesce([Middle], '') & [Last] + additionally space and the other wanted information. If it's not NULL you could replace coalesce() with: if(len(trim([Middle])), [Middle], '').

- Marcus

[MrBosch]

Perfect Marcus, works like a charm. Thank you very much.

Alex

[MrBosch]

And perhaps to add a little extra to make it 100% functional:

= 'Full name: ' &[first name] &' ' &coalesce([middle name], '') & if(len([middle name] > 0), ' ') &[last name] & Chr(10)