How to assign alphabet characters to date ranges

[#1]

Hi All,

  I have a data table that consists of dated entries where the most recent entry is at the top of the table and the oldest entry is at the bottom.

  What I'm trying to do is loop through the rows to find the date difference between two rows. I've done this with the following code, got it from a post that @txnelson answered some time ago (sorry don't have the OP in front of me).

dt = Data Table( "My Data" );
For( i=N Rows(dt), i >= 2, i--,
	:ct[i] = Abs(DateDifference(:date[i-1], :date[i], "Day"));
);

  This works great, and I can find all date differences of interest.

  However, what I really want to do is to label date ranges with alphabet letters (A, B, C, D, etc.).

  For example, if my first entry (last row in data table) starts on 01/01/2017 (dd/mm/yyyy), I want to label that row "A", and want to continue labeling the rows going up the column until it reaches a point where the difference in dates is >=n (in this case n=2, but could be any number bigger than 1). 

  Keep in mind that the dates are increasing as we work up from the bottom of the table. So, the next row above would be 02/01/2017. Let's say this goes on until the end of January, so there are 31 rows going from 01/01/2017 to 31/01/2017.

  At the next row above 31/01/2017, we have 05/02/2017. So the date difference is 5. I would like to then have my label tracker start labeling it as "B" until the next date change where the difference is >=2 and then start labeling as "C", and so on until the data table has finished.

  Something close to this was also posted by @txnelson in this thread: https://community.jmp.com/t5/Discussions/Labeling-rows-as-greater-less-than-a-date-with-JSL/td-p/363..., but it requires a specified start date to compare other dates to. I don't want to have a specified start date, but rather have it done automatically based on whatever dates have populated the table.

  Any suggestions/support is greatly appreciated.

Thanks!

[#2 · Accepted solution]

Here is a script that should do what you want......make sure you study it so you understand how the different function work, and how the logic of the code works.  In other words, don't just cut and paste it without understanding it.  

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
	Add Rows( 100 ),
	New Column( "Date",
		Numeric,
		"Continuous",
		Format( "ddMonyyyy", 12 ),
		Input Format( "ddMonyyyy" ),
		Set Values(
			[3640106008.56964, 3639892909.24216, 3639816069.76636, 3639711971.2654,
			3639614050.02423, 3639594631.39805, 3639581856.84272, 3639499697.65438,
			3639405448.76553, 3639293346.42584, 3639022373.8664, 3638682552.62729,
			3638662956.28802, 3638644514.74731, 3638510978.46425, 3638508355.329,
			3638447240.64829, 3638280760.96257, 3638253465.01226, 3638182573.0004,
			3638135159.78165, 3638031154.80142, 3638019646.52908, 3637888422.23898,
			3637821622.60523, 3637761224.6898, 3637708154.53698, 3637655683.55496,
			3637523013.28917, 3637336766.86203, 3637273948.44657, 3637146868.92602,
			3637139130.85435, 3637041997.93449, 3636718392.96078, 3636702006.57404,
			3636684337.39444, 3636645617.3044, 3636621923.04457, 3636576772.74921,
			3636548097.39841, 3636524282.13857, 3636202501.07711, 3636082870.01646,
			3636015305.10949, 3635986264.04419, 3635954006.90437, 3635864908.69012,
			3635855262.80858, 3635593939.60592, 3635374128.02522, 3635336114.35042,
			3635271204.82971, 3635136811.26229, 3635062537.71875, 3635022035.10663,
			3634999736.12185, 3634972464.2373, 3634849211.45924, 3634794399.03536,
			3634785984.37775, 3634667869.53241, 3634645627.14123, 3634606120.34103,
			3634483420.7033, 3634469476.65781, 3634457506.03348, 3634300180.05399,
			3634161262.31866, 3634024215.74686, 3633993153.4734, 3633957176.13649,
			3633780205.72602, 3633775930.38711, 3633762315.48745, 3633671471.68225,
			3633623618.86685, 3633564003.73824, 3633498484.02555, 3633488261.49554,
			3633127296.72145, 3633091315.52892, 3632836504.4496, 3632759089.11,
			3632695080.2713, 3632564084.614, 3632496376.27829, 3632409035.04661,
			3632213416.86148, 3632155851.6491, 3632136908.29403, 3632061846.54667,
			3632011684.6858, 3631978413.53537, 3631925136.87974, 3631865971.06053,
			3631774901.06017, 3631755982.44004, 3631706805.99126, 3631667141.16235]
		),
		Set Display Width( 68 )
	)
);

// Create the new column
dt<<new column("Label", character);

// Create a list of the alphabet characters
alphalist = {"A", "B", "C", "D", "E", "F", "G", "H",
	"I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S",
	"T", "U", "V", "W", "X", "Y", "Z"};

// Initialize the starting letter
letter=1;

// Loop from bottom to top of the table finding the difference values
// in the data column
For( i =N Rows( dt ), i >= 1, i--,
	If( i == N Rows( dt ),
		// Last row in the table gets the letter "A"
		dt:Label[i] = alphaList[letter],
		// Otherwise
		// If the date on this row is >= the date on the last row, 
		// change the letter number
		If( Abs( Date Difference( :date[i + 1], :date[i], "Day" ) ) >= 2,
			letter = letter + 1
		);
		
		// Since there could be more than 26 groups, groups AA, BB etc.
		// may have to be added, so calculate the number of letters 
		howmanyLetters = Floor( Letter / 26 ) + 1;
		
		// Now calculate which letter to use
		whichLetter = Modulo( letter, 26 );
		If( whichLetter == 0,
			whichLetter = 26
		);
		
		// Create the new value for the column Label
		For( k = 1, k <= howmanyLetters, k++,
			dt:Label[i] = dt:Label[i] || alphaList[whichLetter]
		);
	)
);

[#3]

@txnelson Thank you very much for your code. I followed it very clearly, it was well documented and made sense. Tested it out on the sample data table you made and it worked great.

I did have to slightly modify it for my specific data format as it's a large data table that is concatenated and sometimes the date range loops back so the date difference is very large (>100 days). Nonetheless, it was easy to modify as your comments helped out a lot.

I was actually starting to do something very similar to what your solution is, but was getting there much slower than you did! I think that's why the logic of your code made so much sense.

I really appreciate the help, thank you!