Forum Discussion
How to createtable from one table but filtering based on values from another table
Hello Friendly Helpers,
I have been racking my brain and the interwebs and the youtubes for this. I am former DB dev, and i could so this faily easily in SQL, but alas I am using the tools available to me. So I have a table with the following data:
This table has all employees in it with the id of the employee they report to.
| AllEmployees | ||
| EmployeeID | EmployeeName | ManagerID |
| 1 | A | 99 |
| 2 | B | 1 |
| 3 | C | 1 |
| 4 | D | 2 |
| 5 | E | 2 |
| 6 | F | 3 |
| 7 | G | 3 |
| 8 | H | 9 |
| 9 | I | 9 |
| 10 | J | 9 |
I created a table that where the managerID = 1 from the AllEmployees table.
| Level1Managers | ||
| EmployeeID | EmployeeName | ManagerID |
| 2 | B | 1 |
| 3 | C | 1 |
On this table, i would like to create a table that is populated with employees that have the ManagerID that is in Level1Managers.
| Level2Managers | ||
| EmployeeID | EmployeeName | ManagerID |
| 4 | D | 2 |
| 5 | E | 2 |
| 6 | F | 3 |
| 7 | G | 3 |
So essentially i am planning on creating an org hierarchy out of this, going many many times over until it is complete.
For now, i would like to create the 3rd table. I have been using createtable, but i am not sure how to put a list of dynamic values in the validation portion of the expression.
I tried:
Essentially i want to bring back the all the values from the AllEmployees table where ManagerID = LevelOneManagers.EmployeeID
Hi rdwd15,
Have you considered using PATH as a basis to build your org heirachy?
Heirachy = PATH(Employee[EmployeeID],Employee[ManagerID])Let me know if that helps
To further expand on the above, splitting it can be done using PATHITEM (Index from left) or PATHITEMREVERSE (Index from right).
Manager 1 = PATHITEMREVERSE(Employee[Heirachy],2) Manager 2 = PATHITEMREVERSE(Employee[Heirachy],3) Manager 3 = PATHITEMREVERSE(Employee[Heirachy],4)
6 Replies
- jsaunders_zero9
Responsive Resident
Hi rdwd15,
Have you considered using PATH as a basis to build your org heirachy?
Heirachy = PATH(Employee[EmployeeID],Employee[ManagerID])Let me know if that helps
- jsaunders_zero9
To further expand on the above, splitting it can be done using PATHITEM (Index from left) or PATHITEMREVERSE (Index from right).
Manager 1 = PATHITEMREVERSE(Employee[Heirachy],2) Manager 2 = PATHITEMREVERSE(Employee[Heirachy],3) Manager 3 = PATHITEMREVERSE(Employee[Heirachy],4)
Thanks, that totally looks like what I want to do but with so many fewer complicated steps.
I will give this a try.
- jsaunders_zero9
Good to hear rdwd15, be sure to check back to let me know how you went.
Hello J. Thanks so much, yes this will work exactly how i want it to, and so much easier than my long conviluted way i was going about it.
Thanks again.
