Extracting multiples occurrence of the same given keyword from a String

[Team_SmartStats]

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 :slightly_smiling_face:

Thanks again !

[Dimitris_Goudis]

Hi @Team_SmartStats,

welcome to Airtable community.

The table “Main” is the table where you want to present yours results, right?
First of all you have to put a limitation in all linking records that request data from the table “All”. The limitation will be in requesting only only the latest five game results as you have described in your post. At the moment you request more than 5.

If I understood correctly you want to result the the following: “team name: W-L-W-L…”.
while the sort of the result will be based on chronological sorting based on the game date, right?
To do this you have to request the “game date” field in order to use this info in ordering results. All those results can be listed in the same field with no spiting in “home results” and “away results”. There inside you will see the final you are looking for,

In case that home/away result matters for you, then you have to take it in to account in results ordering before you import those in the final field.

I whish this worked for you, otherwise do not hesitate to text us back your result.

[Team_SmartStats]

Hi @Dimitris_Goudis

Thanks a lot for your reply !

Yes the main (or Team) table is where I want to present the results, Ideally it would be presented like this " W-L-W-D-L".

How can I put a limitation on the linking records ? I haven’t found any option to do that.

I have an hard time visualizing the solution you gave me.
Where do I request the “game date” field ?
What kind of formula or Rollup do I need to use to extract and format the data I need form the Game Table into the Team Table ?
Could you show me an example ?

Thanks again for your help !