How to Concatenate String over Multiple Rows by Group?

[#1]

Hello,

I have a list of product (> 100) with different product codes of each product. I would like to create new column "Product Code Range" that it concatenates the product code for each product as shown below example. How to write JSL code that can do this task?

[#2 · Accepted solution]

I have written addin which can help with thisGroup Listagg Columns . It is using sql-query to perform LISTAGG operation. You could install the addin and take code from there. Basic idea is something like this:

Names Default To Here(1);

dt = Current Data Table();	

custom_sql = Eval Insert(
"\[SELECT Product, GROUP_CONCAT(DISTINCT CAST("Product Code" as TEXT)) as "Product Code Range"
FROM t1
GROUP BY Product
]\");

dt_concat = Query(
	Table(dt, "t1"),
	custom_sql
);

and then join dt_concat back to the original table

JSL Syntax Reference > SQL Functions Available for JMP Queries > Aggregate SQL Functions 

[#3 · Accepted solution]

Other option could be to create Associative Array with Products as keys and Product Codes in a list as values and then fill new column with that. This requires JMP16 due to For Each, but it can be replaced with For-loop if necessary

Names Default To Here(1);

dt = Current Data Table();
aa = Associative Array(:Product << get values);

For Each({{key, value}}, aa,
	aa[key] = dt[Loc(dt[0, "Product"], key), "Product Code"];
);

dt << New Column("Product Code Range", Character, Nominal, << Set Each Value(
	Concat Items(aa[:Product], "/");
));

[#4 · Accepted solution]

One more option came to my mind which won't require any scripting (but can be easily scripted/JMP can mostly script it) (note that order will most likely change with this method).

1. Split the original data

2. Select all the columns which start by P and use Cols/Combine Columns to create new column

3. Join new table with the new combined column to the original data

4. Result:

[#5]

Jarmo,

Thank you. All solutions provided is great.

It will help me in my learning. 

[#6 · Accepted solution]

And here is yet another way to do this, using Get Rows Where()

New Column( "PRC",
	character,
	set each value(
		theProduct = :Product[Row()];
		Concat Items(
			:Product Code[Current Data Table() << get rows where( :Product == theProduct )],
			"/"
		);
	)
);

[#7]

Jim.

It is great. Thank you.

I learn new thing today