Choose Language Hide Translation Bar
Highlighted
lwx228
Level VII

How can the comparison result be obtained directly without adding auxiliary columns?

Hello, everyone!

The yield of the two tables of the same specification is different,
Can the comparison result be obtained directly without adding auxiliary columns:
Results are obtained in table 2: if the yield in table 1 is larger than that in table 2, the result is 1; otherwise, the result is 0.

 

d1 = Open( "$SAMPLE_DATA/Trial1.jmp" );
d2 = Open( "$SAMPLE_DATA/Trial2.jmp" );

 

Thanks!2019-05-28_14-04-29.jpg

11 REPLIES 11
Highlighted
txnelson
Super User

Re: How can the comparison result be obtained directly without adding auxiliary columns?

I am a little confused by what you mean when you say "obtained directly without adding auxiliary columns";  I have put together 2 simple scripts.  The first one adheres strictly to your statement, in that it creates no unnecessary columns to produce the results.  However, this method is inefficient, and will not scale up very well if the data tables get large

Names Default To Here( 1 );

d1 = Open( "$SAMPLE_DATA/Trial1.jmp" );
d2 = Open( "$SAMPLE_DATA/Trial2.jmp" );

// Create the new column by comparing values between the data tables
d2 << New Column( "Result",
	formula(
		compare = d1:yield[((d1 <<
		get rows where(
			d1:popcorn == d2:popcorn & d1:oil amt == d2:oil amt & 
			d1:batch == d2:batch ))[Row()])];
		If( compare > d2:yield[Row()],
			theResult = 1,
			theResult = 0
		);
		theResult;
	)
);

// Change the values of the column Result to static values by removing
// the formula
d2:Result << delete property( "formula" ); 

The second example is very efficient, but uses temporary additional columns, but the results end up the same:

Names Default To Here( 1 );

d1 = Open( "$SAMPLE_DATA/Trial1.jmp" );
d2 = Open( "$SAMPLE_DATA/Trial2.jmp" );

// Temp. change of the name of the Yield column in data table 1
d1:yield << set name("yield1");

// Copy the Yield1 column to data table 2
d2 << Update(
	With( d1 ),
	Match Columns( :popcorn = :popcorn, :oil amt = :oil amt, :batch = :batch ),
	Add Columns from Update table( :yield1 )
);

// Change the Yield1 column in data table 1 back to it's original name
d1:yield1 << set name("yield");

// Create and calculate the Result column in data table 2
d2 << new column("Result", formula(
	If(:yield1 > :yield, 1,0);
));

// Change the values of the column Result to static values by removing
// the formula
d2:Result << delete property( "formula" );

// Delete the no longer needed Yield1 column
d2<<delete columns("Yield1");

 

Jim
Highlighted
lwx228
Level VII

Re: How can the comparison result be obtained directly without adding auxiliary columns?

Thank Jim!
So the first way I'm going to think about it is when you have a lot of data in a row, you can use memory to do it faster.
Highlighted
lwx228
Level VII

Re: How can the comparison result be obtained directly without adding auxiliary columns?

And I'm going to ask you again, I tried the first method, and there's no result.I don't know where I made a mistake, thanks!
Highlighted
lwx228
Level VII

Re: How can the comparison result be obtained directly without adding auxiliary columns?

The result of the first code operation

Thank you!

2019-05-28_21-50-36.jpg

Highlighted
txnelson
Super User

Re: How can the comparison result be obtained directly without adding auxiliary columns?

What messages are in the JMP Log?

 

Also, there was a miss copy on the last line of the code for the first method

    " The Second"

was miss copied.  Make sure it isn't in your code.

Jim
Highlighted
lwx228
Level VII

Re: How can the comparison result be obtained directly without adding auxiliary columns?

invalid subscript (must be number or list of numbers){1} in access or evaluation of '(d1 << get rows where(d1:popcorn == d2:popcorn & d1:oil amt == d2:oil amt & d1:batch == d2:batch))[Row()]' , (d1 << get rows where(
d1:popcorn == d2:popcorn & d1:oil amt == d2:oil amt & d1:batch == d2:batch
))[/*###*/Row()]

 

Thank Jim!

2019-05-29_6-08-48.jpg

Highlighted
lwx228
Level VII

Re: How can the comparison result be obtained directly without adding auxiliary columns?

That seems to be the mistak2019-05-29_6-45-38.png

Highlighted
txnelson
Super User

Re: How can the comparison result be obtained directly without adding auxiliary columns?

I don't know what to say. I have run my code under JMP Pro all the way back to JMP 11, and can not get it to fail. I am running on Window's 10
You may need to take this to JMP Support to be able to further investigate the discrepancy.
Jim
Highlighted
lwx228
Level VII

Re: How can the comparison result be obtained directly without adding auxiliary columns?

I see. Thank Jim!
Article Labels

    There are no labels assigned to this post.