Forum Discussion
Count between tables
Hi I have a large data set that lists a customer, product(s) and employee assignment. each employee needs to be counted per product and have a weighting applied. however it is possible that the...
What are trying to do would be hard with the structure you have. I would suggest unpivoting it so that you have a structure like the following:
Customer Product Employee
Customer ABC 1 Product 1 Joe Blogs
Customer ABC 1 Product 3 Caty Biggs
Customer ABC 2 Product 2 Caty Biggs
Customer ABC 2 Product 4 Joe Blogs
Then you can do distinct count of customers per employee or apply percentages based on the product
What are trying to do would be hard with the structure you have. I would suggest unpivoting it so that you have a structure like the following:
| Customer | Product | Employee |
| Customer ABC 1 | Product 1 | Joe Blogs |
| Customer ABC 1 | Product 3 | Caty Biggs |
| Customer ABC 2 | Product 2 | Caty Biggs |
| Customer ABC 2 | Product 4 | Joe Blogs |
|
Then you can do distinct count of customers per employee or apply percentages based on the product