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

Group values from selected rows

Jackie_

Hi,

I am trying to group values from the selected rows

Here is the dt:

The final output should look something like this. So combine the Product values with similar percent. Any suggestion?

dt = Current Data Table();

dt << select duplicate rows( Match( :Label, :Percent ) );

Try( dt<< delete rows );

product_dt.jmp

Accepted answers

Jackie_

Closing this

Here is one way

Names Default To Here( 1 );

dt = Current Data Table();
aa = Associative Array( :Percent << get values );

For Each( {{key, value}}, aa, aa[key] = dt[Loc( dt[0, "Percent"], key ), "Product"] );

dt << New Column( "Product Code Range", Character, Nominal, <<Set Each Value( Concat Items( aa[:Percent], ", " ) ) );

dt << select duplicate rows( Match( :Label, :Percent ) );
Try( dt << delete rows );

All comments

Jackie_

Closing this

Here is one way

Names Default To Here( 1 );

dt = Current Data Table();
aa = Associative Array( :Percent << get values );

For Each( {{key, value}}, aa, aa[key] = dt[Loc( dt[0, "Percent"], key ), "Product"] );

dt << New Column( "Product Code Range", Character, Nominal, <<Set Each Value( Concat Items( aa[:Percent], ", " ) ) );

dt << select duplicate rows( Match( :Label, :Percent ) );
Try( dt << delete rows );

txnelson

Having done this kind of thing before, I suggest that you round the Percent column before grouping them. Percent values of .5 and .5001 might be formatted to be displayed as .50 but they will create different groups. Sometimes I have found that such a script will require not only the rounding, but also the converting to a character column for the code to be robust.

hogi

Nice workaround

character "aggregations" like this one - with just a few mouse clicks?

Here is the wish:
https://community.jmp.com/t5/JMP-Wish-List/Summary-and-Tabulate-add-aggregation-option-for-Character/idi-p/659314

jthi

I do this type of aggregations in JMP using SQL (and like Jim said, converting to characters (or rounding) most likely should be done. One of the reasons being Floating Point Math (30000000000000004.com))

Names Default To Here(1);

dt = Open("$DOWNLOADS/product_dt.jmp");

dt << New Column("Selected", Numeric, Nominal, Formula(
	Selected()
));

dt_result = Query(
	Table(dt, "dt"),
	"\[
SELECT Percent, Label, GROUP_CONCAT(Product, ',') AS Product
FROM dt
WHERE Selected = 1
GROUP BY Percent, Label
	]\"
);