I'm using a formula on the row number to only increment the ID if the formula condition matches.
This is the result i want to generate:
I have a flat table of game events
there is a sortie_id column used to identify each player that has the event 'TAKEOFF'
When a player 'Takes Off' the sortie_id should be incremented by 1. But each sortie_id should be related to a single player when they took off.
so for example
id, event, player, sortie_id
1, KILL, 1, 12
2, SLING, 2, 13
3, LAND, 2, 13
4, TAKEOFF, 2, 14
5, CRASH, 1, 12
6, TAKEOFF, 1, 15
player 1 had a kill, and then crashed during sortie 12, player 2 sling loaded and landed in sortie 13, player 2 took off in sortie 14, and player 1 took off in sortie 15.
I think this is probably going to involve generating multiple data sets, so is there a way of doing this while also being able to generate random events too? (everything that is not 'TAKEOFF')