0

Counting Data with Delimiters

I have data that is formatted so that the people associated with a row are separated by a "," delimiter

| Date       | Result | Employees     |
|------------|--------|---------------|
| 2020-01-01 | PASS | Alice |
| 2020-01-02 | FAIL | Bob,Charlie |
| 2020-01-03 | PASS | Alice,Charlie |

And I would like to create a table that counts how many passes and fails are associated with each employee.

| Employee | Pass | Fail |
|----------|------|------|
| Alice | 2 | 0 |
| Bob | 0 | 1 |
| Charlie | 1 | 1 |

What I've tried is I have a list of employees from a different data source that creates the first column, but I don't know how to write the formula to get something like

COUNT(SELECT(@Result WHERE CONTAINS(@Employees, Employee) AND RESULT = "Pass"))

2 comments

  • 0
    Avatar
    Yvonne Van Alphen

    Hi Austin

    How big is your data set?  One way to do this in a table is as follows.

    For the employee column, use the formula:

    which will give you each employee's name

    For the pass column, use the formula:
    which will return the number of passes for each employee's name.

    For the fail column, you can use the same formula, but update the word PASS to FAIL.

    The finished table looks something like this:
    If you would like me to take a look at your specific Klip, I am happy to do so through a support request (email: support@klipfolio.com).

    Yvonne

  • 0
    Avatar
    Austin Nhung

    Hi Yvonne,

    Thank you for the advice. Unfortunately the data source is several thousand rows long, so using each employee's name separately will be untenable. Is there no dynamic way to reference the employee name in the first column? If not, then I will submit a support request.

Please sign in to leave a comment.