944092_en-US

liliconcom

concatenating cells

I am trying to convert rows grouped by a composite variable into a concatenated cell sepearated by a space based on the grouping variable.  Example:

into:

 

 \

 

The code below creates a grouping column and a "N rows" column but i cant get the contents of the cells concatenated.

 

dt = Current Data Table();

dt_summary = dt << Summary(
Group( :Column B ),
Statistics(
Concatenate( :Column A, " " )
)
);

 

 

 

Data Blending and CleanupRe: concatenating cells

You could do it this way.  This accounts for the possibility of duplicates in Column A and only lists each item once.

 

Names Default To Here( 1 );
dt = Current Data Table();
Summarize( dt, cats = By( :Column B ) );
dt_summary = New Table( "Summary", New Column( "Category", Character, "Nominal" ), New Column( "Items", Character, "Nominal" ) );
For Each( {v, i}, cats,
	dt_summary << Add Rows(
		{:Category = v, :Items = Concat Items( Associative Array( dt:Column A[Where( dt, :Column B == v )] ) << Get Keys, " " )}
	)
);

Re: concatenating cells

I like doing this with SQL Query from JMP table

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

dt_concat = Query(
	Table(dt, "t1"),
	"\[
SELECT age, GROUP_CONCAT(sex) as g,
count(*) as n
FROM t1
GROUP BY age
	]\"
);

You can also ignore duplicates by adding DISTINCT to the group concat

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

dt_concat = Query(
	Table(dt, "t1"),
	"\[
SELECT age, GROUP_CONCAT(DISTINCT sex) as g,
count(*) as n
FROM t1
GROUP BY age
	]\"
);

 

Re: concatenating cells

thanks for the reply.  havent done a SQL query but will give it a try.

Re: concatenating cells

There is also a wish list item Add Listagg/Concatenate/GROUP_CONCAT to Summary platform