Hi,
I using Custom SQL to query a database for employee records. A simplified example of the data looks like this:
id|detail|manager|manager_manager|
--|------|-------|---------------|
1|US | 3| 4|
2|CA | 3| 4|
3|UK | 4| 5|
4|US | 5| 6|
There are 25+ details available for a given employee. I would want to reference the main ID column for any number of other ID columns to get details. An ideal calculated field would produce a new column like below:
id|detail|manager|manager_manager|manager_detail |manager_manager_detail|
--|------|-------|---------------|---------------|----------------------|
1|US | 3| 4|UK |US |
2|CA | 3| 4|UK |US |
3|UK | 4| 5|US |NULL since no 5 |
4|US | 5| 6|NULL since no 5|NULL since no 6 |
I could do all the joining in SQL, but then the table gets pretty wide and extract creation becomes suboptimal. I also considered blending, but I would want to dynamically blend on any given ID relationship (id-manager, id-manager_manager, etc). Duplicating that datasource would then require more database resources to be used to refresh the same thing.
Is it possible to generate something like this with an LOD? I've attached a sample workbook. Thanks!
EDIT: making scenarios clearer
