Forum Discussion
Post PatronGet values from 3 tables into one matrix
I need to get the sum of three columns from two different tables and can't figure out why I'm struggling with it.
Tables are: Employees
| EmployeeID | Location |
| MINN0001 | Loc1 |
| MINN0001 | Loc2 |
| SMIT0001 | Loc1 |
| JONE0001 | Loc1 |
Table 2: Amt Pd:
| EmployeeID | Location | Color | Date | AmtPd |
| MINN0001 | Loc1 | Green | 12/31/21 | 5.00 |
| SMIT0001 | Loc1 | Green | 12/31/21 | 5.00 |
| MINN0001 | Loc1 | Blue | 12/31/21 | 10.00 |
| SMIT0001 | Loc1 | Blue | 12/31/21 | 8.00 |
| JONE0001 | Loc1 | Green | 12/31/21 | 12.00 |
| MINN0001 | Loc2 | Blue | 12/31/21 | 7.00 |
Table3: Amt Earned
| EmployeeID | Location | Date | Color | AmtEarned | AmtEr | |||||
| MINN0001 | Loc1 | 12/5/21 | Blue | 2.00 | 1.00 | |||||
| MINN0001 | Loc1 | 12/17/21 | Blue | 3.00 | 1.00 | |||||
| MINN0001 | Loc1 | 12/5/21 | Green | 7.00 | 5.00 | |||||
| SMIT0001 | Loc1 | 12/17/21 | Blue | 3.00 | 1.00 | |||||
| SMIT0001 | Loc1 | 12/5/21 | Green | 5.00 | 2.00 | |||||
| JONE0001 | Loc1 | 12/5/21 | Blue | 0 | 5.00 | |||||
Result I need is a matrix: (Choosing Blue and Loc1)
| EmployeeID | Location | Date | Pd | Earned | Er |
| MINN0001 | Loc1 | 12/31/21 | 10.00 | 5.00 | 2.00 |
| SMIT0001 | Loc1 | 12/31/21 | 8.00 | 3.00 | 1.00 |
| JONE0001 | Loc1 | 12/31/21 | 0.00 | 0.00 | 5.00 |
Result I need is a matrix: (Choosing Green and Loc1)
| EmployeeID | Location | Date | Pd | Earned | Er |
| MINN0001 | Loc1 | 12/31/21 | 5.00 | 7.00 | 5.00 |
| SMIT0001 | Loc1 | 12/31/21 | 5.00 | 5.00 | 2.00 |
| JONE0001 | Loc1 | 12/31/21 | 12.00 | 0.00 | 0.00 |
I know I can need to get the end of the month from the paid column but for the life of me I can't figure out how to do the relationships to get the matrix options I need.
Hi kattlees
I add EmployeeLoc columns to all three tables by combining EmployeeID and Location columns. And build relationships on these new columns. Also I add dim tables for colors and locations. Download the attachment below to see details.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
v-jingzhangCommunity Support
Hi kattlees
I add EmployeeLoc columns to all three tables by combining EmployeeID and Location columns. And build relationships on these new columns. Also I add dim tables for colors and locations. Download the attachment below to see details.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
