761526_en-US

liliconcom

Group values from selected rows

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

 

 

Automation and ScriptingRe: Group values from selected rows

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=""></SET>

 

Re: Group values from selected rows

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 rowsNice 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

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