community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Order number field within a group

Highlighted
Asteroid

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 idproduct id
1005234
1006987
1004465
1017899
1026891
1023233

 

 

So the final result would look like this:

 

lookup idorder numproduct id
10015234
10026987
10034465
10117899
10216891
10223233

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

 

Quasar
Quasar

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

 

multirow.png

Asteroid

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

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?

Alteryx Certified Partner
Alteryx Certified Partner

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

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).

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).

Labels