topic Re: Combine Data by Row in Discussions

Combine Data by Row

Alex19 — Tue, 10 Dec 2024 14:22:37 GMT

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.

Re: Combine Data by Row

hogi — Tue, 10 Dec 2024 14:38:35 GMT

Very common question.

There are a lot of tricks and workarounds.

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

... if enough users follow the link and vote.

Re: Combine Data by Row

Jackie_ — Tue, 10 Dec 2024 14:48:17 GMT

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

Re: Combine Data by Row

hogi — Tue, 10 Dec 2024 14:47:09 GMT

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

Re: Combine Data by Row

Alex19 — Tue, 10 Dec 2024 14:48:28 GMT

Works beautifully! Thank you!