Record ID with grouping

[jesuisbo]

Hi all,

I've been trying to figure this out on my own but without any luck so far... 

I'm trying to apply the record ID 1 & 2 for every two lines/rows - in order for me to make a calculated field that assigns "left" to 1 and "right" to 2. The below table example shows what I'm trying to accomplish in the orange column. The counting would reset every new [GameNo].

match_id	ElapsedTime	SetNo	GameNo	PointNumber	PointServer	ServeIndicator	serve_number	Serve_#
2019-usopen-2101	0:13:03	1	4	20	1	1	1	1
2019-usopen-2101	0:13:28	1	4	21	1	1	1	2
2019-usopen-2101	0:13:45	1	4	22	1	1	1	1
2019-usopen-2101	0:14:02	1	4	23	1	1	1	2
2019-usopen-2101	0:14:37	1	4	24	1	2	1	1
2019-usopen-2101	0:20:44	1	6	31	1	1	1	1
2019-usopen-2101	0:21:23	1	6	32	1	1	1	2
2019-usopen-2101	0:21:39	1	6	33	1	1	1	1
2019-usopen-2101	0:22:19	1	6	34	1	1	1	2
2019-usopen-2101	0:23:34	1	6	36	1	2	1	1

I tried the multi-row formula, smart tiles, a lot of things, but I just can't seem to figure it out. 

Does anyone know what I should do?

Thank you!!

[OllieClarke · Accepted solution]

Hi @jesuisbo a multi-row formula, grouped by GameNo, with the following formula should do it:

IF [row-1:serve_#]=1 THEN 2 ELSE 1 ENDIF

[jesuisbo]

Amazing - this worked! Thank you @OllieClarke!

[CharlieS]

The Tile tool is my favorite tool, so I've attached a workflow that shows how it can be used here.

- Use the "Unique Value" configuration to assign a Group By RecordID to each game.

- A Formula tool can convert the RecordID values into the requested "1/2" designations.  

[chuck_bible84]

I knew there had to be a tool that did this, thank you!

[Qiu]

@jesuisbo 
Try this macro.

https://marketplace.alteryx.com/en-US/apps/420292/crew-grouped-record-id