topic Hierarchy with Group by? in App Development

Hierarchy with Group by?

click-er — Fri, 02 Apr 2021 07:54:25 GMT

Is it possible to use the hierarchy expression WITH a group by in a load statement? For instance i have 12 monthly snapshots of employees in one table and i'd like to build the hierarchy for each month (i.e., the group by).

I tried this, but it didn't work. Load failed.

Hierarchy Table:

Hierarchy([employee_id], [supervisor_id], [name],'Manager',[name],'Hierarchy Path','/','Depth')
left Join
LOAD Distinct [supervisor_id]
DATE(effective) as effective,
Count([employee_id]) as TotalDirectReports
Resident Workers
Group By [supervisor_id],DATE(effective);

Re: Hierarchy with Group by?

jonathandienst — Wed, 24 Apr 2019 06:30:07 GMT

I am not sure what you are trying to do, but Hierarchy(..) is a prefix to a load statement with (at the very least), a node id and a parent id, along with other fields such as node names. It is not a complete load statement, so you can't left join to it.

When you are grouping by superviser id, you are collapsing the hierarchy, so the hierarchy will not work.

Its possible that this would work, but i cant say for sure as you have not provided enough information:

Hierarchy([employee_id], [supervisor_id], [name],'Manager',[name],'Hierarchy Path','/','Depth')
LOAD * 
Resident Workers

left Join
LOAD Distinct [supervisor_id]
DATE(effective) as effective,
Count([employee_id]) as TotalDirectReports
Resident Workers
Group By [supervisor_id],DATE(effective);

Also try

Hierarchy(employee_id, supervisor_id, name)
...

Re: Hierarchy with Group by?

click-er — Wed, 24 Apr 2019 12:26:09 GMT

Many thanks. However, I should have been more specific. The Workers table has a snapshot of employees in Jan. And another snapshot of employees in Feb, March, April...Dec. etc. for all 12 months. So i need to apply the hierarchy 12 times (i.e., that is why I need to group by each Month (e.g., Date(effective). And then I was simply trying to join this table to the resident table (worker). Hope that helps explain what i was trying to do. Any more guidance would be appreciated.