Combine Data by Row

[#1]

Hello, 

I am trying to do the following:

Given Product Column and Lot Column, I need to create "Lots in Product" Column so that it combines all the "lots' based on the same product.

The final purpose is to get the Tabulate:

The X1, X2, X3, X4 does not have to be present in the single cell, this will work as well (whichever is easier):

Manually this process is insane when I have a table of thousands of datapoints.

Thank you so much!

JMP table is attached.

Alex.

[#2]

Very common question.

There are a lot of tricks and workarounds.

In a future version of JMP it will be very easy:

🙏 Summary and Tabulate: add aggregation option for Character columns 
... if enough users follow the link and vote.

[#3 · Accepted solution]

Here is one way

Names Default To Here(1);

dt = Current Data Table();	

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

dt_concat = Query(
	Table(dt, "t1"),
	custom_sql
);New window("Tablebox",<<Type("Dialog"),Data Table Box( dt_concat ));

[#4]

another one:

dt = Current Data Table();
dt << New Column( "rank", Formula( Col Rank( 1, :Product ) ), );

dtsplit = dt << Split(
	Split By( :rank ),
	Split( :Lot ),
	Group( :Product ),
	Remaining Columns( Drop All ), 

);

cols = dt split << get column names();
cols = cols[2 :: N Items( cols )];

dt split << New Column( "concat", Character, Set Each value( Concat Items( cols, "," ) ) );

dt << Update(
	With( dt split),
	Match Columns( :Product = :Product ),
	Add Columns from Update Table( :concat ),
	Replace Columns in Main Table( None )
);

[#5]

Works beautifully! Thank you!