cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
bernie426
Level II

How to use Dif/Lag function by Column group

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

BoxiClassBinPosSlot
72813_2111164
73213_215152
73213_215153
73213_215154
73213_215155
73213_215156
73213_215157
73213_215158
73214_214141
73214_214142
73214_214143
73214_214144
73214_214145
73214_214146
73213_215159
73214_214147
73213_2151510
73213_2151511
73213_2151512
73213_2151513
73213_2151514
73213_2151515
73214_214148
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

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.

12099_pastedImage_0.png

12100_pastedImage_1.png

12107_pastedImage_2.png

Jim

View solution in original post

11 REPLIES 11
mpl34
Level III

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

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

txnelson
Super User

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

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,

       1,

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

);

Jim
bernie426
Level II

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

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,

       1,

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

)));

Regards,

txnelson
Super User

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

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.

12099_pastedImage_0.png

12100_pastedImage_1.png

12107_pastedImage_2.png

Jim
bernie426
Level II

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

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,

txnelson
Super User

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

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 );

Jim
pmroz
Super User

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

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.

txnelson
Super User

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

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]]
);
Jim
miguello
Level VI

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

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, 

M.