- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Combine Data by Row
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Combine Data by Row
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 ));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Combine Data by Row
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Combine Data by Row
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 ));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Combine Data by Row
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 )
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Combine Data by Row
Works beautifully! Thank you!