Problem with a column formula to assign production phases

[#1]

Hi All,

  I'm trying to write a column formula that will look at the difference in date between two rows and if >= than a certain value (in this case 4 days), then the column should decrement the phase value. If the date difference is <4 then it should keep the previous phase value. I've defined two local variables in the formula: phase and MinDaysbreak, which is the minimum days between phases (4). The column :"Date/Time:"n is the date column that the For() loop is iterating through.

  My formula looks like the following:

  And the JSL code for the formula is the following:

For( i = N Rows(), i >= 1, i--,
	If( i == N Rows(),
		phase,
		If( Abs( Date Difference( :"Date/Time:"n[i + 1], :"Date/Time:"n, "Day" ) ) >= MinDaysbreak,
			phase--,
			phase
		)
	)
)

  I'm not getting any errors for the formula, the formula is not suppressed, but the column is still empty. I still get nothing for the last row even if I remove the first else statement in the first If(), and the last entry for the column should be 9, as that's the local variable phase. I know that there are 9 phases.

  I'm not sure what I'm doing wrong, but I wouldn't be surprised if it's a simple mistake that I'm overlooking.

Thanks for any help!,

DS

[#2 · Accepted solution]

The column is empty because the For() function does not return a value. Try pasting this code into the formula editor:

Local({i = N Rows(), phase = 9, MinDaysbreak = 4},
    For(i, i >= Row(), i--,
        If(i == N Rows(),
            phase,
            If(Abs(Date Difference(:"Date/Time:"n[i + 1], :"Date/Time:"n[i], "Day")) >= MinDaysbreak,
                phase--,
                phase
            )
        )
    );
    phase;
)

I think it will work as intended. Note these changes:

– The additional phase that sets the value of the current row (after been calculated within the For loop). Without this you get an empty column.

– To work correctly you should not iterate over all rows, only down to the current row using Row().  Otherwise you will get the same value for every row.

– You need an index for both dates to make sure to always compare with the next row. Without it is hard to predict the outcome (depends on the data).

I also defined initial i as a local parameter, but that's not necessary I think. 

However, it's quite unusual to use a for loop in column formulas. If you have a large data set it can be very inefficient to loop over the table in order to set the value for every row. There are other ways to solve this problem, with column formula or JSL.

/ms 

[#3]

Hi @ms2 ,

  Thanks for reviewing my code and providing helpful feedback. Yes, it did work as intended, I did notice I a couple mistakes I had made in the code -- particularly the index for the current row comparison, but even with those fixed, I don't think it would have worked.

  After defining the local parameters i, phase, and so on, JMP will automatically pull that "phrase" out of the JSL and store them as local parameters, as if you would have done it manually.

  I do have a similar code I run with JSL and it works great, but I was trying to modify it to be more portable with the table for when it's shared with others. Not many other people in my company use JSL, so sometimes I need to do things differently. Although, I suppose I could write a table script that does it and that should be faster and portable -- hadn't thought about that until now.

  Anyway, thanks for your feedback and help!

DS