topic Re: How to Concatenate String over Multiple Rows by Group? in Discussions

How to Concatenate String over Multiple Rows by Group?

Lino — Fri, 09 Jun 2023 17:02:14 GMT

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?

Re: How to Concatenate String over Multiple Rows by Group?

jthi — Thu, 23 Jun 2022 13:03:26 GMT

I have written addin which can help with this . 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

Re: How to Concatenate String over Multiple Rows by Group?

jthi — Thu, 23 Jun 2022 13:31:02 GMT

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], "/");
));

Re: How to Concatenate String over Multiple Rows by Group?

jthi — Thu, 23 Jun 2022 16:56:10 GMT

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:

Re: How to Concatenate String over Multiple Rows by Group?

txnelson — Thu, 23 Jun 2022 17:49:11 GMT

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 )],
			"/"
		);
	)
);

Re: How to Concatenate String over Multiple Rows by Group?

Lino — Thu, 23 Jun 2022 23:22:43 GMT

Jarmo,

Thank you. All solutions provided is great.

It will help me in my learning.

Re: How to Concatenate String over Multiple Rows by Group?

Lino — Thu, 23 Jun 2022 23:24:39 GMT

Jim.

It is great. Thank you.

I learn new thing today