topic How to use Dif/Lag function by Column group in Discussions

How to use Dif/Lag function by Column group

bernie426 — Fri, 29 Jul 2016 19:31:19 GMT

Hi JMP community folks,

I have a data table with four columns as Box, iClass, BinPos, and Slot. iClass means 24 level of products, BinPos means the Box location in the sorting equipment and Slot indicated which slot position the product be placed in the box. The Box is designed to hold a max quantity of 100 product, after that a new empty box would be placed in. In order to check the product is be placed in the right box and the Slot increment is exactly by "1" (because sometime I do manually observed the slot increment increase >1 or increment=0, which meant the placing machine having issues),I tried to use the dif/lag function to check the increment. However, the dif/lag formula is not allowing me the add a function to group by column of Box. The Box is actually the unique identifier.

A formula like Dif(:Slot,1) can tell me the difference b/w a certain with respect to the row before it. However, the rows are mixed by with different Box because of difference level of product belong to iClass column.

I am hoping to have a formula like Dif(:Slot, 1, :Box) to work out. Can any expert provide some clues to work this out? A example table is attached for investigation.

Many Thanks in advance

Box	iClass	BinPos	Slot
72813_2	11	11	64
73213_2	15	15	2
73213_2	15	15	3
73213_2	15	15	4
73213_2	15	15	5
73213_2	15	15	6
73213_2	15	15	7
73213_2	15	15	8
73214_2	14	14	1
73214_2	14	14	2
73214_2	14	14	3
73214_2	14	14	4
73214_2	14	14	5
73214_2	14	14	6
73213_2	15	15	9
73214_2	14	14	7
73213_2	15	15	10
73213_2	15	15	11
73213_2	15	15	12
73213_2	15	15	13
73213_2	15	15	14
73213_2	15	15	15
73214_2	14	14	8

Re: How to use Dif/Lag function by Column group

mpl34 — Fri, 29 Jul 2016 20:35:45 GMT

Can you sort by :Box and then apply the formula after checking that :Box==Lag(:Box,1)?

Re: How to use Dif/Lag function by Column group

txnelson — Fri, 29 Jul 2016 23:08:38 GMT

If the absolute order of the original data is not important, sorting the data and using the Dif function will work. However, here is a formula that you can use that will calculate the increment without having to sort the data.

i = Row();

allrows = Current Data Table() << get rows where( :Box == :Box[i] );

If( Loc( As List( allrows ), i )[1] == 1,

slot[allrows[Loc( As List( allrows ), i )[1]]] - slot[allrows[(Loc( As List( allrows ), i )[1])-1]]

);

Re: How to use Dif/Lag function by Column group

bernie426 — Fri, 29 Jul 2016 23:28:39 GMT

Hello Jim,

I tried to run the script you made (just copy and pasted the entire script), however, I cannot see anything change in the data table and no New Column to generate.

I tried to make a new column with your script but nothing work either.

This is what the script I tried to run,

i = Row();

allrows = Current Data Table() << get rows where( :Box_ID == :Box_ID );

allrows<< New Column("Test", Numeric, Continuous, Formula(If( Loc( As List( allrows ), i )[1] == 1,

slot[allrows[Loc( As List( allrows ), i )[1]]] - slot[allrows[(Loc( As List( allrows ), i )[1])-1]]

)));

Regards,

Re: How to use Dif/Lag function by Column group

txnelson — Wed, 19 Oct 2016 03:31:51 GMT

The example is a formula for a new table, not a JSL standalone script. Create a new column, and paste the formula into the formula for the column, and it will generate the incremental values you want.

Re: How to use Dif/Lag function by Column group

bernie426 — Sat, 30 Jul 2016 03:47:39 GMT

Hi Jim,

Thanks for the help. Actually, I have more than 60,000+ rows in a single daily table if I run a formula you provide it would take a fairly long time.

Would it be quicker if I sort the data first then run the formula?

Btw, is there a way for jmp a count the time it run for a script or formula?

Regards,

Re: How to use Dif/Lag function by Column group

txnelson — Sat, 30 Jul 2016 13:34:50 GMT

With that many rows, using the sort method would be faster. Below is the method that I use to find the time a script takes:

start = today();

the script or part of a script you want to find the timings on

show( today() - start );

Re: How to use Dif/Lag function by Column group

pmroz — Mon, 01 Aug 2016 14:42:34 GMT

If you need more accuracy in the timing, @msharp noted in a recent post:

Today() is only accurate to the second, Tick Seconds() is accurate to 1/60 of a second, and HP TIME() is accurate to the microsecond.

Re: How to use Dif/Lag function by Column group

miguello — Mon, 11 Jun 2018 23:40:09 GMT

Jim,

This is a very valuable workaround since it provides a functionality of a non-existen formula ColLag(); :)

However, I see it's been quite a while since you posted this workaround. JMP 14 nowadays doesn't like how it's written. It says Get Rows Where is an unknown function and it says that using Current Data Table() in formulas is not stable.

Is there any newer version of this workaround?

Thanks,

Re: How to use Dif/Lag function by Column group

miguello — Tue, 12 Jun 2018 00:09:49 GMT

Jim,

I take it back - Formula wasn't working because it didn't know Get Rows Where expression. As soon as I changed the colum type (it was a date, so I really didn't care) to Numeric, it worked, even though it still doesn't like the Get Rows Where.

Basically, what I'm trying to do is to find the previous date by Column.

I have a table of parts replacement history on different tools:

Tool	Part	Install Date	Remove Date
Tool A	Part 1	03/03/2015	???
Tool B	Part 1	04/04/2015	???
Tool A	Part 2	05/05/2015	???
Tool A	Part 1	06/06/2015	???
Tool A	Part 2	07/07/2015	???

So, for the same tool and the same part, I need to get the NEXT install date and put it as a Remove Date for current part.

Now, I'm not sure how do you make sure that the it actually looks up the NEXT date. Formula works with the minimal modifications as I can tell on the limited observations, but as far as I understand the formula, it wouldn't...

Could you please help me with clenaing this up?

Thanks,

Re: How to use Dif/Lag function by Column group

txnelson — Tue, 12 Jun 2018 03:33:13 GMT

Here is a little script that should give you the results you want for the Remove Date

Names Default To Here( 1 );
dt = Current Data Table();

// Add a sequence column to capture the current
// order of the data table
dt << New Column( "tableorder", formula( Row() ) );
// Convert the values in the tableorder column to real values
dt:tableorder << delete formula;

// Sort the table in ascending order
dt = dt << Sort(
	By( :Tool, :Part, :Install Date ),
	Order( Ascending, Ascending, Ascending ),
	replace table( 1 )
);

// Create the Remove Date Column
dt << New Column( "Remove Date", Format( "m/d/y", 10 ) );

// Loop across all of the rows and set the Remove Date
For( i = 2, i <= N Rows( dt ), i++,
	If( :Tool[i] == :Tool[i - 1] & :Part[i] == :Part[i - 1],
		:Remove Date[i - 1] = :install Date[i]
	)
);

// Sort the table back to original order
dt = dt << Sort( By( :tableorder ), Order( Ascending ), replace table( 1 ) );

// Remove the tableorder column
dt << delete columns( "tableorder" );

Re: How to use Dif/Lag function by Column group

txnelson — Tue, 12 Jun 2018 03:35:31 GMT

Here is a rework of the previous formula. This formula works with JMP 14

If( Row() == 1,
	dt = Current Data Table()
);
i = Row();
holdBox = :Box;
allrows = dt << get rows where( holdBox == :Box[i] );
If( Loc( As List( allrows ), i )[1] == 1,
	1,
	:Slot[allrows[Loc( As List( allrows ), i )[1]]] - :Slot[allrows[Loc( As List( allrows ), i )[1] - 1]]
);