topic Re: How to Concatenate Multiple String Variables within a Row in the Script in QlikView

How to Concatenate Multiple String Variables within a Row in the Script

Fri, 14 Apr 2017 18:21:28 GMT

Greetings,

I am trying to figure out how to concatenate multiple string variables within a row, in the script, and only show comma delimiters where the variable is populated (i.e. it is not null). I know that you can use ampersands for concatenation, but that does not work without writing a lot of conditional code. Also, I am not trying to collapse values across different rows (which is what the CONCAT function appears to be for). I just want the concatenation to occur within each row/observation. Is there a function or some relatively simple code to handle this? Below is a table that shows some fake data, and the desired output.

Variable 1	Variable 2	Variable 3	Variable 4	Desired Variable
AA	BB	CC		AA, BB, CC
	BB		DD	BB, DD
		CC		CC
AA	BB	CC	DD	AA, BB, CC, DD
AA			DD	AA, DD
	BB			BB

Thank you for your time,

Patrick

Re: How to Concatenate Multiple String Variables within a Row in the Script

Kushal_Chawda — Sat, 15 Apr 2017 09:44:26 GMT

one way could be

MapSTring:

mapping LOAD * Inline [

From,To

"|,",

",|" ];

temp1:

LOAD *,

MapSubString('MapSTring',ConcatString) as String;

LOAD [Variable 1],

if(len(trim([Variable 1]))=0,'|',[Variable 1]) &','&

if(len(trim([Variable 2]))=0,'|',[Variable 2]) &','&

if(len(trim([Variable 3]))=0,'|',[Variable 3]) &','&

if(len(trim([Variable 4]))=0,'|',[Variable 4]) as ConcatString,

[Variable 2],

[Variable 3],

[Variable 4]

FROM

[https://community.qlik.com/thread/257128]

(html, codepage is 1252, embedded labels, table is @1);

Re: How to Concatenate Multiple String Variables within a Row in the Script

swuehl — Sat, 15 Apr 2017 10:28:53 GMT

Another option would be to transform your table using CROSSTABLE LOAD prefix, then use Concat() to aggregate across the rows:

temp1:

CROSSTABLE(Variable, Value)

LOAD Recno() as RecID,

[Variable 1],

[Variable 2],

[Variable 3],

[Variable 4]

FROM

[https://community.qlik.com/thread/257128]

(html, codepage is 1252, embedded labels, table is @1);

LOAD RecID, Concat( Value, ', ', FieldIndex('Variable',Variable)) as ConcatString

Resident temp1

Where Len(Trim(Value))

GROUP BY RecID;

DROP TABLE temp1;

The Crosstable Load