How to combine data from diffrent colums in one

1

I am having following data set

image

Now I want to filter the data in new table where I want to see how many jobs person 1, 2 and 3 is working on

Ex - person 1 is working on job 1 and job 3.

2

Dear @Video_by_Pro,

Welcome to the community :handshake:

With concatenate as explained in this post you will be able to to solve this.

3

I am new to Coda and I am using grouping option. But it’s not serving the purpose. I am not sure how to implement concatenate. I am looking for result to look like this

image

4

Hey @Video_by_Pro, welcome to the Community.

First, a question: do you need to have Employer one and Employer two as two separate single-select columns? In Coda, you can have one column Employers with multi-select option.

Second, you don’t need a separate table. You already have a table of Employers. So what you need to do is make a lookup column that would pull in all the relevant projects from the Jobs table:

Jobs.Filter(CurrentValue.[Employer one] = thisRow OR CurrentValue[Employer two] = thisRow)

Or if you had a multi-select Employers column on a Jobs table, the lookup would be:

Jobs.Filter(CurrentValue.Employers.Contains(thisRow))

and actually you wouldn’t even have to write it yourself, but could add a column with this formula already populated there via the new column type menu.

When you’ve pulled in the jobs, you can further calculate e.g. .Count() on those (or also just add a column via the new column menu)

5

Thanks for the solution. I made a new table and implemented this formula but I am getting blank result.

6

This is not a formula for a new table. This is a formula for a column for an existing table that you’re pulling your Person 1/2/3 from.

You don’t do this on a separate table. The “Jobs for this person” and the “Count of jobs for this person” belong on the table that lists those persons. Of course on a separate table thisRow won’t be any of the persons entries that you selected in Jobs.

7

Got it. Thanks!
Here is the case scenario. I am having one table and one view. Master table is having Jobs and Employers working on that. Now, I am making a view to know about how many employers are working on how many jobs. So, I used the filter but its working only for one column and Multi select don’t show accurate data.

8

It would help if you shared the doc. The scenario sounds like a simple and well supported one. Perhaps you’re just misunderstanding some fundamental concept of Coda. It can be steep if you never designed actual databases.

9

Dear @Video_by_Pro,

A quick way to get the hang of Coda is in this video course
And as Paul suggested share a dummy doc and write down what kind of info you want to get from your data.

Jobs are mostly called tasks and they are kept in a project.

After that you will be able to see how many tasks are in a project, how much time it total takes, the earliest start and last end date - total duration of project, how many people worked on the project, who did the most tasks, who did have no tasks assigned, …

The community will be glad to support after you have set the initial directions

10

Hello @Paul_Danyliuk and @Jean_Pierre_Traets
Thanks for your help.

Please find the doc below

I want to show the jobs for each employer

11

Dear @Video_by_Pro,

I amended your doc and put some explanations.

If something remains unclear, feel free to get back to us :rocket:

1 Like
12

Thank you so much. Let me review this and get back to you.