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

Question

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?OHO112973YOHO1129111 OHO112972YOHO112971YOHO1129114 OHO1129113 OHO112969YOHO1129109 OHO112970YOHO1129116 OHO1129115 OHO1129110 OHO112974 OHO112968YOHO1129112 OHO1181126 OHO1181129 OHO1181130 OHO1181128 OHO1181127 OHO1181126 OHO118193YOHO1181129 OHO118192YOHO118196YOHO118191YOHO1181100 OHO118199 OHO118197 OHO118194YOHO1181128 OHO1181127 OHO118198 OHO118195YOHO119179 OHO119177 OHO119178 OHO119182 OHO119183 OHO119184 OHO119181 OHO119176 OHO119180 OHO119137 OHO119136 OHO119135 OHO119134YOHO119133YOHO119131YOHO119130YOHO119129YOHO119132YOHO119176 OHO119175 OHO1192161 OHO1192157 OHO119271YOHO1192153 OHO119268YOHO1192156 OHO1192155 OHO119273 OHO119267YOHO1192160 OHO119272YOHO1192158 OHO119269YOHO119274 OHO1192159 OHO1192151 OHO119270Y

txnelson · Accepted Answer

Here is one way to solve your issue.

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

jthi · Accepted Answer

You can also use Col Rank combined with if:

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