topic how to sumerize the number column with of identical value per row in jmp in Discussions

how to sumerize the number column with of identical value per row in jmp

Cheli — Thu, 08 Jun 2023 16:39:17 GMT

who know how to summarize the number column with of identical value per row in jmp?

for example:

Movie	Year	Domestic $	###	###	SUM
Ocean's Eleven	2001	$ 183.40	$ 444.30	$ 183.40	2
Airport	1970	$ 100.50	$ 100.50	$ 100.50	3
Armageddon	1998	$ 201.60	$ 554.60	$ 271.60	0
Batman	1989	$ 251.20	$ 413.00	$ 413.00	2
Batman & Robin	1997	$ 107.30	$ 237.30	$ 107.30	2
Batman Forever	1995	$ 335.00	$ 335.00	$ 335.00	3

thanks

Cheli

Re: how to sumerize the number column with of identical value per row in jmp

txnelson — Wed, 01 Feb 2023 17:05:38 GMT

Assuming you are running an interactive JMP session, simply double click on the column header of the column in question, and change the name to whatever name it should have.

Re: how to sumerize the number column with of identical value per row in jmp

Cheli — Wed, 01 Feb 2023 19:10:38 GMT

thanks. but I didn't explain my question well.

I need to summarize the number of columns with identical occurrence for each row.

I need to create columne same as "SUM" at my example

Re: how to sumerize the number column with of identical value per row in jmp

jthi — Wed, 01 Feb 2023 20:54:14 GMT

If you have only three columns you could use Choose with checking of how many unique values there are. I'm using (N Items(Associative Array(Eval List({:A,:B,:C})) << get keys)) for that here

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("Movie", Character, "Nominal", Set Values({"Ocean's Eleven", "Airport", "Armageddon", "Batman", "Batman & Robin", "Batman Forever"})),
	New Column("Year", Numeric, "Continuous", Format("Best", 12), Set Values([2001, 1970, 1998, 1989, 1997, 1995])),
	New Column("A", Numeric, "Continuous", Set Values([183.4, 100.5, 201.6, 251.2, 107.3, 335])),
	New Column("B", Numeric, "Continuous", Set Values([444.3, 100.5, 554.6, 413, 237.3, 335])),
	New Column("C", Numeric, "Continuous", Set Values([183.4, 100.5, 271.6, 413, 107.3, 335])),
	New Column("SUM", Numeric, "Continuous", Format("Best", 12), Set Values([2, 3, 0, 2, 2, 3]))
);

dt << New Column("SUM2", Numeric, Continuous, Formula(
	Choose(N Items(Associative Array(Eval List({:A,:B,:C})) << get keys),
		3,
		2,
		0
	)
));

Edit:

As @txnelson said, Associative Array doesn't really work with non integer numeric values unless you transform the values somehow (you could try converting them to characters first or multiply). With three columns it will be short to write a formula with comparisons (like Jim did). Also instead of Associative Array you could use Design

dt << New Column("SUM2", Numeric, Continuous, Formula(
	val = Max(V Sum(Design(Eval List({:A,:B,:C}))));
	If(val > 1,
		val
	,
		0
	);
));

Re: how to sumerize the number column with of identical value per row in jmp

txnelson — Wed, 01 Feb 2023 20:11:39 GMT

An issue arises with @jthi code when the comparison values in the Associative Array are not integers. The keys for Associative Arrays are forced to integer values. The decimal values are truncated. Therefore, A=100,4, B=100.5, C=100.4 returns a 3, not a 2 as it should be. If the significant number of decimal values are known, a multiplier can be added to the formula to force the values to be integers

Choose(N Items((Associative Array(Eval List({:A*10,:B*10,:C*10})) << get keys)

or a restructuring of the code to use Boolean math.

dt << New Column( "SUM2",
	Numeric,
	Continuous,
	Formula( If( :A == :B & :B == :C , 3, :A == :B | :A == :C | :B == :C, 2, 0 )
));