907861_en-US

liliconcom

Concatenation of rows by using JMP menues or JSL

How can text in a column "A" in different rows be concatenated, if the grouping value in another column "B" is equal and if the text in column "A" in the 2 rows are different?:

 

B       C          A 

A1     Mix 1       Salt

A1     Mix 1       Sugar

A1     Mix 2       Salt

A1     Mix 2       Sugar

 

 

 

I want to output to be two rows:

B          C        A_combined

A1      Mix 1    Salt, Sugar

A1      Mix 2    Salt, Sugar

 

 

In practice, I have more columns than column B and C to be grouped by. ChatGPT says, that I can use Table Summary and under statistics choose concatenate, but that option is not available.

 

I have JMP 18.1.1

Automation and ScriptingBasic Data Analysis and ModelingRe: Concatenation of rows by using JMP menues or JSL

A) use Query Builder

vote here:
:folded_hands: Summary and Tabulate: add aggregation option for Character columns 

Re: Concatenation of rows by using JMP menues or JSL

SQL Query:

 

 

Names Default To Here(1);

dt = New Table( "test",
	Add Rows( 4 ),
	New Column( "A",
		Character,
		Set Values( {"A1", "A1", "A1", "A1"} )
	),
	New Column( "B",
		Character,
		Set Values( {"Mix 1", "Mix 1", "Mix 2", "Mix 2"} )
	),
	New Column( "C",
		Character,
		Set Values( {"Salt", "Sugar", "Salt", "Sugar"} )
	)
);
Query(
	dt,
	"SELECT A, B, GROUP_CONCAT(C, ', ') as A_combined FROM 'test'
    group by A, B"
);

Re: Concatenation of rows by using JMP menues or JSL

If I'm understanding correctly, one way to do this is to use Tables > Transpose, then the Cols > Utilities > Combine Columns to do this, with a few extra columns created/deleted along the way. I've attached a sample workflow and data table that goes through this step by step.