Forum Discussion
Return a value from one table based on two columns, one with a delimiter
Hello, I have two tables. One is a auto generated call data, the other is a list of outcomes for calls.
I need to somehow create a column for the Auto generated Call data that compares both the Start/End Time of the call and the Time of submission for the outcome (Needs to be inbetween the Start/End Time) as well as the name of person who submitted the outcome vs names in the Call Data to match records and return the Outcome value matching those records.
However the names in call data can have multiple names within a cell seperated by a semicolon delimiter depending on who touched the call.
Here is an example of tables.
Call Data
| Names | Start Time | End Time |
| John; Nancy; Frank | 5/28/2024 12:00 PM | 5/28/2024 12:30 PM |
| Frank | 5/28/2024 1:00 PM | 5/28/2024 1:10 PM |
| George; Frank; Nancy; Thomas | 5/28/2024 1:05 PM | 5/28/2024 1:10 PM |
| Hubert; Tony | 5/28/2024 1:10 PM | 5/28/2024 1:25 PM |
Outcome Data
| Name | Time | Outcome |
| Nancy | 5/28/2024 12:15 PM | Complete |
| Frank | 5/28/2024 1:09 PM | Incomplete |
| Thomas | 5/28/2024 1:07 PM | Hung Up |
| Hubert | 5/28/2024 1:17 PM | Complete |
Desired Calculated Column Outcome
| Names | Start Time | End Time | Calculated Outcome |
| John; Nancy; Frank | 5/28/2024 12:00 PM | 5/28/2024 12:30 PM | Complete |
| Frank | 5/28/2024 1:00 PM | 5/28/2024 1:10 PM | Incomplete |
| George; Frank; Nancy; Thomas | 5/28/2024 1:05 PM | 5/28/2024 1:10 PM | Hung Up |
| Hubert; Tony | 5/28/2024 1:10 PM | 5/28/2024 1:25 PM | Complete |
JBusque , Please try a new column in call table (First Table)
Maxx(filter(Outcome, Call[Start Time]<= Outcome[Time] && Call[End Time]<= Outcome[Time] && Search(Outcome[Name], call[Names],,0) >0) , Outcome[Outcome])
2 Replies
JBusque , Please try a new column in call table (First Table)
Maxx(filter(Outcome, Call[Start Time]<= Outcome[Time] && Call[End Time]<= Outcome[Time] && Search(Outcome[Name], call[Names],,0) >0) , Outcome[Outcome])
Yes this worked quite well, Thank you.
