cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
qspringleaf
Level III

about JSL formula to mark lowest 6 numbers within each fixed Item

I have one table with column "ITEM" and "ID", and the "ID" column value is random, could help to suggest how to use JSL formula to create one new column, and fill in content "Y" when the "ID" value is the lowest 6 within each fixed "ITEM" value. thanks a lot

 

ITEMID6 low ID within each Fixed Item?
OHO112973Y
OHO1129111 
OHO112972Y
OHO112971Y
OHO1129114 
OHO1129113 
OHO112969Y
OHO1129109 
OHO112970Y
OHO1129116 
OHO1129115 
OHO1129110 
OHO112974 
OHO112968Y
OHO1129112 
OHO1181126 
OHO1181129 
OHO1181130 
OHO1181128 
OHO1181127 
OHO1181126 
OHO118193Y
OHO1181129 
OHO118192Y
OHO118196Y
OHO118191Y
OHO1181100 
OHO118199 
OHO118197 
OHO118194Y
OHO1181128 
OHO1181127 
OHO118198 
OHO118195Y
OHO119179 
OHO119177 
OHO119178 
OHO119182 
OHO119183 
OHO119184 
OHO119181 
OHO119176 
OHO119180 
OHO119137 
OHO119136 
OHO119135 
OHO119134Y
OHO119133Y
OHO119131Y
OHO119130Y
OHO119129Y
OHO119132Y
OHO119176 
OHO119175 
OHO1192161 
OHO1192157 
OHO119271Y
OHO1192153 
OHO119268Y
OHO1192156 
OHO1192155 
OHO119273 
OHO119267Y
OHO1192160 
OHO119272Y
OHO1192158 
OHO119269Y
OHO119274 
OHO1192159 
OHO1192151 
OHO119270Y
2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: about JSL formula to mark lowest 6 numbers within each fixed Item

Here is one way to solve your issue.

txnelson_0-1627441274078.png

Here is the formula I used

If( Row() == 1 | Lag( :ITEM ) != :ITEM,
	currentItem = :ITEM;
	theRows = Current Data Table() << get rows where( :ITEM == currentItem );
	For( i = 1, i <= N Rows( theRows ), i++,
		theRows[i] = :ID[theRows[i]]
	);
	r = Rank( theRows );
	theValues = theRows[r];
	Remove From( theValues, 7, 9999999 );
);
If( N Rows( Loc( theValues, :ID ) ) > 0,
	"Y",
	""
);

I have attached the sample data table shown above

 

 

Jim

View solution in original post

jthi
Super User

Re: about JSL formula to mark lowest 6 numbers within each fixed Item

You can also use Col Rank combined with if:

tempRank = Col Rank(:ID, :ITEM);
If(tempRank <= 6, "Y", "");
-Jarmo

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: about JSL formula to mark lowest 6 numbers within each fixed Item

Here is one way to solve your issue.

txnelson_0-1627441274078.png

Here is the formula I used

If( Row() == 1 | Lag( :ITEM ) != :ITEM,
	currentItem = :ITEM;
	theRows = Current Data Table() << get rows where( :ITEM == currentItem );
	For( i = 1, i <= N Rows( theRows ), i++,
		theRows[i] = :ID[theRows[i]]
	);
	r = Rank( theRows );
	theValues = theRows[r];
	Remove From( theValues, 7, 9999999 );
);
If( N Rows( Loc( theValues, :ID ) ) > 0,
	"Y",
	""
);

I have attached the sample data table shown above

 

 

Jim
jthi
Super User

Re: about JSL formula to mark lowest 6 numbers within each fixed Item

You can also use Col Rank combined with if:

tempRank = Col Rank(:ID, :ITEM);
If(tempRank <= 6, "Y", "");
-Jarmo
txnelson
Super User

Re: about JSL formula to mark lowest 6 numbers within each fixed Item

Better answer

Jim
Craige_Hales
Super User

Re: about JSL formula to mark lowest 6 numbers within each fixed Item

Very nice, I was only barely aware of the 2nd argument, ByVar, to the Col functions. ColMax has it too, so the top 6 can be selected. (The by groups have different number of rows, a constant won't work.) max and rank and flipped are column names; the formula's column name is in the window title. The column name is used in subsequent formulas.

The formulas could be collapsed into one column; add parenthesis as needed. Expanded like this is easier to understand.The formulas could be collapsed into one column; add parenthesis as needed. Expanded like this is easier to understand.

ColRank has a rule for ranking duplicates; if you care about duplicates on the edge of top or bottom 6, check it out. In the 2nd by group, the ID 127 kept row 32 but not row 20 because of the flip. Without the flip, the rank would keep the earlier row.

Craige