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

[#1]

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

[Kushal_Chawda]

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);

[swuehl · Accepted solution]

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