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

- JMP User Community
- :
- Discussions
- :
- How to find weight measured nearest to specific dates?

- 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 to find weight measured nearest to specific dates?

Oct 19, 2018 2:25 PM
(7233 views)

Hi JMP Community :-)

Below is a table representing the data I'm looking at (in a lot smaller scale). **I wish to create a list of the weights measured on the dates closest to "Arrival dates" for the specific IDs.**

I used JMP to get my data in this order, and I tried combining Excels INDEX, MATCH and OFFSET functions to get my desired list, but can't seem to get the right result.

Hope someone out there can help :-)

Kind regards,

Rae

ID | ARRIVAL DATE | DATE1 | WEIGHT1 | DATE2 | WEIGHT2 | DATE3 | WEIGHT3 | DATE4 | WEIGHT4 | DATE NEAREST ARRIVAL | WEIGHT NEAREST ARRIVAL | |

12345678 | 41109.58 | 43108.55 | 60 | 39681.38 | 62 | 38227.88 | 58 | 42507.28 | 61 | |||

23456781 | 42634.77 | 42509.9 | 100 | 42634.77 | 111 | 41948.6 | 105 | 40128.4 | 112 | |||

34567812 | 38087.28 | 42234.54 | 45 | 41578.41 | 46 | 38087.28 | 45.5 | 41733.03 | 47 | |||

45678123 | 41221.33 | 40842.34 | 58 | 42575.53 | 57 | 41221.33 | 58.5 | 42003.35 | 58 | |||

56781234 | 42025.37 | 42025.37 | 65 | 39900.69 | 66 | 40925.14 | 65 | 41464.54 | 67 | |||

67812345 | 38821.74 | 39075.54 | 70 | 43005.43 | 71 | 41185.52 | 69 | 38821.74 | 70 | |||

78123456 | 39074.11 | 40477.9 | 150 | 39074.11 | 151 | 39310.43 | 153 | 41505.26 | 150 | |||

81234567 | 42515.49 | 37998.55 | 92 | 41824.97 | 89 | 42626.11 | 93 | 41051.34 | 92 |

9 REPLIES 9

Highlighted
##

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

Re: How to find weight measured nearest to specific dates?

If I am understanding exactly what you need, I think the attached data table has the formulas for the 2 new columns you want that will produce the below results:

Jim

Highlighted
##

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

Re: How to find weight measured nearest to specific dates?

Wow! You deserve a medal! AMAZING! Thank you SO much!

Highlighted
##

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

Re: How to find weight measured nearest to specific dates?

Wow! You deserve a medal! AMAZING! Thank you SO much txnelson!

Highlighted
##

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

Re: How to find weight measured nearest to specific dates?

Hi txnelson,

First of all thanks for taking the time to answer.

I am working with Raeish on this.

Could the formula be tweaked for another variable to only find the date nearest (before) but not equal to arrival date and the corresponding value at that time?

Highlighted
##

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

Re: How to find weight measured nearest to specific dates?

Attached is the same data table as I attached before, now with new formulas.

Please take the time to read the documentation on formula creation in

Help==>Books==>Using JMP

and

Help==>Books==>Scripting Guide

Jim

Highlighted
##

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

Re: How to find weight measured nearest to specific dates?

Hi Jim :-) Again, thank you so much for taking your time to reply :-)

We will definitely read about formula creation - Thank you for the tip!

As for the last file you sent, I've tried adding it to the previous formula - but it doesn't seem to change anything. You wrote that you sent the same data __table__ as before(just with a new formula) but when I open it I only get this short formula, not the entire table. Is that right?

names default to here(1);

dt=current data table();

for each row(

dateMatrix = Matrix(:Date1,:Date2,:Date3,:Date4);

Show(dateMatrix);

)

Thank you again,

Kind regards

- Rae

Highlighted
##

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

Re: How to find weight measured nearest to specific dates?

My error....I attached the wrong file....I think the new attachement will work better

Jim

Highlighted
##

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

Re: How to find weight measured nearest to specific dates?

Great! THANK YOU!

One more question: is there a way to add the criteria that the weight nearest arrival has to be within 2 days of arrival(and if no such date exist leave it blank)?

Kind regards,

Rae

Highlighted
##

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

Re: How to find weight measured nearest to specific dates?

Attached is a new version of the formulas. I am not sure if your date columns are JMP date/time columns. I made the assumption they are, and therefore, I use the In Datys() function to calculate the number of days to subtract for the comparison. If your column is not a JMP date/time value, you need to change the subtraction to the value for a day.

Jim

Article Labels

There are no labels assigned to this post.