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

Question

Hi

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

for example:

MovieYearDomestic $######SUMOcean's Eleven2001$ 183.40$ 444.30$ 183.402Airport1970$ 100.50$ 100.50$ 100.503Armageddon1998$ 201.60$ 554.60$ 271.600Batman1989$ 251.20$ 413.00$ 413.002Batman & Robin1997$ 107.30$ 237.30$ 107.302Batman Forever1995$ 335.00$ 335.00$ 335.003

thanks

Cheli

txnelson · Answer

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

jthi · Answer

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

Cheli · Answer

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