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

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

qspringleaf — Fri, 09 Jun 2023 19:53:28 GMT

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

ITEM	ID	6 low ID within each Fixed Item?
OHO1129	73	Y
OHO1129	111
OHO1129	72	Y
OHO1129	71	Y
OHO1129	114
OHO1129	113
OHO1129	69	Y
OHO1129	109
OHO1129	70	Y
OHO1129	116
OHO1129	115
OHO1129	110
OHO1129	74
OHO1129	68	Y
OHO1129	112
OHO1181	126
OHO1181	129
OHO1181	130
OHO1181	128
OHO1181	127
OHO1181	126
OHO1181	93	Y
OHO1181	129
OHO1181	92	Y
OHO1181	96	Y
OHO1181	91	Y
OHO1181	100
OHO1181	99
OHO1181	97
OHO1181	94	Y
OHO1181	128
OHO1181	127
OHO1181	98
OHO1181	95	Y
OHO1191	79
OHO1191	77
OHO1191	78
OHO1191	82
OHO1191	83
OHO1191	84
OHO1191	81
OHO1191	76
OHO1191	80
OHO1191	37
OHO1191	36
OHO1191	35
OHO1191	34	Y
OHO1191	33	Y
OHO1191	31	Y
OHO1191	30	Y
OHO1191	29	Y
OHO1191	32	Y
OHO1191	76
OHO1191	75
OHO1192	161
OHO1192	157
OHO1192	71	Y
OHO1192	153
OHO1192	68	Y
OHO1192	156
OHO1192	155
OHO1192	73
OHO1192	67	Y
OHO1192	160
OHO1192	72	Y
OHO1192	158
OHO1192	69	Y
OHO1192	74
OHO1192	159
OHO1192	151
OHO1192	70	Y

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

txnelson — Wed, 28 Jul 2021 03:03:13 GMT

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

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

jthi — Wed, 28 Jul 2021 05:39:14 GMT

You can also use Col Rank combined with if:

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

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

txnelson — Wed, 28 Jul 2021 07:30:21 GMT

Better answer

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

Craige_Hales — Wed, 28 Jul 2021 11:16:12 GMT

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.

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.