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
11 REPLIES 11
miguello
Level VI

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

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:

ToolPartInstall DateRemove Date
Tool APart 103/03/2015???
Tool BPart 104/04/2015

???

Tool APart 2 05/05/2015???
Tool APart 106/06/2015???
Tool APart 207/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, 

M.

txnelson
Super User

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

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