Hi everyone and thanks in advance for your help ,
I’m building a Soccer Analytics tool with Airtable
My goal is to extract the results of the 5 last games for a given team.
(For exemple “Win - Win - Lose - Draw - Win” or " W - W - L - D - W")
So far, I’ve build two tables, one that contain the teams info and one that contain the games info.
Each game records is link with two teams (Home Team & Away Team)
I tried using roll-up to achieve my goal, but I was only able to get the result for the games played Away and the games played Home separately and in order to work I need the results to be in chronological order in a single cell.
The closest I was able to get is by formatting the result of each game in a single string with the team names
(ex: Alaves=L/Real Madrid=W)
From here, I’m able to use a lookup in the team tables to get an array of all the results for the games played by a given team. It looks like that :
Alaves=L/Real Madrid=W, Alaves=L/Mallorca=W, Alaves=L/Osasuna=W, Alaves=W/Atletico Madrid=L, Valencia=W/Alaves=L, Espanyol=W/Alaves=L
I tired to use a Regex_Extract function
REGEX_EXTRACT(ARRAYJOIN(Result,),CONCATENATE({Team Name},"…"))
But it only return the first match instead of all of them
(ex : Alaves=L)
Is there a way to make the function return all the occurrences of the Regex ?
I’m sharing my views in case you need to take a better look to the data
Games View : Airtable - All
Team View : Airtable - Main
If you think of a more efficient way to go, I’m very open to change method
Thanks again !