Help! Trying to calculate time differences using two different lists of dates

[#1]

Hi! I have two lists of dates for a project, one is the Maintenance Dates and one is the Production Dates. I'm trying to figure out for each Production Date, how long has it been since the last Maintenance Date. The Maintenance Dates will be far less frequent than Production Dates, obviously, but there are a lot of them so it would be way easier to have a formula to calculate this delta. Anyone have any suggestions?

[#2]

Hi, winslow!

JMP keeps track of datetimes as the number of seconds since midnight, 01January1904.  For instance, right now is 3599388994.  This makes it incredibly easy to keep track of time differences...it just requires some arithmetic and some formatting.  JMP formats support a bunch (14?) different ISO date formats, and you can even parse any unsupported date format into a JMP informat with a formula or script.

You may have more of a challenge putting your two lists together so you can perform all the arithmetic correctly.  Reply if you need any help with that, or if you have questions.

[#3]

If you have 2 lists of dates, one called Prod(production dates) and one called Maint(maintenance dates), you can use the following to get the last maintenance date.  Then using a Date Difference() function, you can find the difference in days

Note:  The Maintenance list/matrix needs to be in ascending order.

names default to here(1);
maint={.............};
prod={..............................};
For(i=1,i<=N Rows(prod),i++,
	LastMaintDate=loc sorted(matrix(maint),matrix(prod[i]));
	DaysDifference = Date Difference(prod,LastMaintDate,"Day");
);

[#4]

Hmm I can't seem to get it to work... Maybe it's because the lists are independently generated and have nothing to do with one another (as far as JMP can tell). I guess let me re-frame my question: I have 1 list with Prod Lot, Prod Date/Time, Prod Equipment. In a separate list, I have Facilities Equipment (not the same thing as Prod Equipment), Facilities Maint Event, Facilities Maint Date/Time. They're two separate lists, I don't have any way of joining (no keys can be generated that would work), but I want to figure out for each Prod Date/Time what was the most recent Facilities Maint Date/Time (and I guess the Facilities Maint Event, although those are all just "on/off" events). Does that make sense? 

[#5]

Do you have your data in JMP lists, or in JMP data tables?  Can you attach samples of your data to the discussion?

[#6]

I can't really attach the data since it is IP... But both datasets are in JMP Tables. 

[#7]

Hi kevin_c_anderso! My problem isn't necessarily finding the difference between dates, but rather that I have two lists of dates that are not connected to one another, and for one list I need the amount of time passed since the closest/most recent event on the other list. List A has more entries than List B, and there are no columns in either list that have anything I can use to join them. I just want to figure out for every item in List A, what is the most recent event in List B, and then I can calculate the amount of time that passed between them. Does that make sense?

[#8 · Accepted solution]

Here are 2 data tables without keys that do a date difference.  Is this what you are looking for?

Names Default To Here( 1 );

// Create some sample date data tables
dtProd = New Table( "Production Data",
	Add Rows( 15 ),
	New Column( "Production Date",
		Numeric,
		"None",
		Format( "ddMonyyyy", 12 ),
		Input Format( "ddMonyyyy" ),
		Set Values(
			[3461875200, 3469737600, 3485462400, 3506630400, 3509049600, 3530217600, 3545942400, 3567110400, 3590697600,
			3606422400, 3614284800, 3651177600, 3659040000, 3666902400, 3688070400]
		)
	)
);
dtMaint = New Table( "Maintenance Data",
	Add Rows( 4 ),
	New Column( "Maintain Date",
		Numeric,
		"Continuous",
		Format( "ddMonyyyy", 12 ),
		Input Format( "ddMonyyyy" ),
		Set Values( [3444163200, 3528316800, 3603744000, 3689539200] )
	)
);

// Create a new column in the Production Data table
dtProd << New Column( "Days since maintenance", Format( "Fixed Dec", 8,0 ) );

// Populate the new column by finding the most recent value of the Maintain Date
// that is less than the current Production Date, and then find the Difference
// in days
For( i = 1, i <= N Rows( dtProd ), i++,
	selectionMatrix = dtMaint << get rows where( dtMaint:Maintain Date <= dtProd:Production Date[i] );
	dtProd:Days since maintenance[i] = Date Difference(
	Max( dtMaint:Maintain Date[selectionMatrix] ),dtProd:Production Date[i], "Day");
);

[#9]

Yes! I think this will work! It will take me a bit to figure out how to get my tables in there (I'm a total noob at jmp :) ) but the principle looks right on. Thanks!