topic Re: Expression - Concat three variables if not empty/null in QlikView

Expression - Concat three variables if not empty/null

MrBosch — Sun, 31 Jul 2022 08:12:20 GMT

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.

Re: Expression - Concat three variables if not empty/null

sidhiq91 — Sun, 31 Jul 2022 09:08:51 GMT

@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.

Re: Expression - Concat three variables if not empty/null

MrBosch — Sun, 31 Jul 2022 10:27:39 GMT

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.

Re: Expression - Concat three variables if not empty/null

thomas8927eva — Mon, 01 Aug 2022 05:32:32 GMT

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

Re: Expression - Concat three variables if not empty/null

marcus_sommer — Mon, 01 Aug 2022 11:24:19 GMT

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

Re: Expression - Concat three variables if not empty/null

MrBosch — Mon, 01 Aug 2022 20:17:33 GMT

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

Alex

Re: Expression - Concat three variables if not empty/null

MrBosch — Tue, 02 Aug 2022 16:12:10 GMT

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)