Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- How can the comparison result be obtained directly without adding auxiliary colu...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Created:
May 28, 2019 1:05 AM
| Last Modified: May 28, 2019 7:53 AM
(1669 views)

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!

11 REPLIES 11

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Created:
May 28, 2019 3:07 AM
| Last Modified: May 28, 2019 7:42 AM
(1659 views)
| Posted in reply to message from lwx228 05-28-2019

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

The result of the first code operation

Thank you!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Created:
May 28, 2019 7:53 AM
| Last Modified: May 28, 2019 7:56 AM
(1633 views)
| Posted in reply to message from lwx228 05-28-2019

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

That seems to be the mistak

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

You may need to take this to JMP Support to be able to further investigate the discrepancy.

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.