Order number field within a group

[rhyatt]

I feel like there is probably a tool that can easily do what I want, but for whatever reason I'm having a hard time finding it.

I'm simply looking to group and then number rows within the group.  For example, in the below table i want to add a column called 'order num' that essentially numbers each item within the group.

lookup id	product id
100	5234
100	6987
100	4465
101	7899
102	6891
102	3233

So the final result would look like this:

lookup id	order num	product id
100	1	5234
100	2	6987
100	3	4465
101	1	7899
102	1	6891
102	2	3233

[NicoleJohnson · Accepted solution]

The Tile tool (in Preparation category) is a good tool for this: Set your tile method to Unique Value, and choose your lookupid as the unique field. The results will give you Tile_Num based on the groups of lookup id's, and then the Tile_SequenceNum will be your record id within each group. :)

NJ

[Joe_Mako]

How about a Multi-Row Formula tool, configured to use the "Group by" option, a formula like in the attached:

[rhyatt]

So for unique value I choose the lookupid, and then also choose that as the 'grouping fields' at the bottom section?

[docadams2009]

I get an error (Parse error) with the code and was wondering the same thing.  The grouping that I'm using is based on a string variable that is a concatenate of  'school system/school/race [alpha]/grade' for the purpose of doing a 1:1 matched-pair analysis.

Are the built-in operations used?

[BenMoss]

can you send over a screenshot of your config and the error that's being triggered?

Parse errors are usually caused by string+double missmatches.

Ben

[docadams2009]

There are two images attached.

Yes, that's probably the problem. The field that I want to parse is a string from the concatenate of four other string variables (system_no,school_no,race_alpha,grade_no)   The school systems reuse the numbering sequence (0010),  and the system designations have leading zeros on a three-digit code.  Converting to numbers prior to concatenating produces mismatched duplicates such that different schools would share the same number.  To create a unique identifier for the school, we have used a concatenate of the strings.  My match will also rely on race ( and inconsistent alphabetical designation that changes by year) and grade (two digits) is named CRZYCON_DSTSCHGDRCG.  It's an ugly mess, but I need the student matches to occur within the same school, within the same race and grade.   

I'm comparing assessments and discipline rates for children of military families to their peers in their respective schools.  The state did not provide a poverty marker, so we cannot match on that dimension.   I'm exploring using the tile function as a substitute.

That field (CRZYCON_DSTSCHGDRCG) sorts beautifully in Excel, with the military/non-military field as a second criterion. If I have five military students in a school with X characteristics, I need five non-military students from that same school with X characteristics (already randomly sorted), and I will discard the unmatched.  I tried this manually in Excel, but need a more efficient way to do it (600,000+ records, but only 9,000 military kids).

[docadams2009]

This is a clearer picture of the sorting/parsing issue.  The last lines will be deleted prior to moving to the next set to match (manually).