topic Re: Group values from selected rows in Discussions

Group values from selected rows

Jackie_ — Sat, 25 May 2024 17:16:41 GMT

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

Re: Group values from selected rows

Jackie_ — Sat, 25 May 2024 22:39:35 GMT

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

Re: Group values from selected rows

txnelson — Sat, 25 May 2024 22:32:05 GMT

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.

Re: Group values from selected rows

hogi — Sun, 26 May 2024 14:16:00 GMT

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

Re: Group values from selected rows

jthi — Mon, 27 May 2024 06:24:20 GMT

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
	]\"
);