nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Combine Data by Row

Alex19

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.

Test.jmp

Accepted answers

Jackie_

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
);<BR /><BR />New window("Tablebox",<<Type("Dialog"),<BR />Data Table Box( dt_concat ));

All comments

hogi

Very common question.

There are a lot of tricks and workarounds.

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

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

Jackie_

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
);<BR /><BR />New window("Tablebox",<<Type("Dialog"),<BR />Data Table Box( dt_concat ));

hogi

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 )
);

Alex19

Works beautifully! Thank you!