first of all thanks for your answer.
I checked your attached .qvw file and I the problem which I also encountered in my solution is that the records in the table with the whole hierarchy are always duplicated. See my attached .qvw with the selected agentID 10000082.
If you apply a time filter, e.g. Year 2015 AND Month Feb, in the hierarchy table only one record should be displayed.
In that period the supervisor of 10000082 were 11000000 only, not 1000000 too !
i found the solution on my problem.
So I just wanted to share how I approached the problem:
- Extracted days from the From -> To Period through the IntervalMatch Function and created a full calendar Agent to SPV table
- Filtered only the monthend Dates from the created table
- For every monthend date I created a Hierarchy Agent Table (used the concatenate prefix to have one resulting Hierarchy Table) -> in this table we have also the Date Field + AgentID
- Created concatenated key field Date + AgentID
In this way I have always the right snapshot of the hierarchy in a given time period.
You can find my app as attachment
hierarchy_scd2.qvw 282.8 K