Hi
I have a hierarchy table which has 3 levels.
| Level 1 | Level 2 | Level 3 |
|---|
| Manager 1 | User 1 | User 4 |
| Manager 1 | User 2 | |
| Manager 2 | | |
| Manager 3 | User 3 | User 5 |
Unfortunately for the model I am building I need to use the 3rd level which isn't always filled. What I want to do: if the value is null I want to pull the value from the next level up to replace the null
| Level 1 | Level 2 | Level 3 |
|---|
| Manager 1 | User 1 | User 4 |
| Manager 1 | User 1 | User 1 |
| Manager 2 | Manager 2 | Manager 2 |
| Manager 3 | User 3 | User 5 |
Below is the code I have come up with (which works) but I cant help thinking there is a simpler/more elegant solution to my problem
Note: The tempSalesHierarchy table is the original table and the code below is just the 'second pass'
[Sales Hierarchy]:
LOAD
if(isnull([%AccountArea]),if(isnull([Level 3 - Area]),[Level 2 - Area],[Level 3 - Area]),[%AccountArea])
as [%AccountArea]
,[%Reduction]
,[Level 1 - Name]
,[Level 1 - Area]
,[Level 1 - Target]
,[Level 1 - Acc#]
,[Tier1]
,[Level 2 - Level 1 Area]
,[Level 2 - Name]
,[Level 2 - Area]
,[Level 2 - Target]
,[Level 2 - Acc#]
,[Tier2]
,if(isnull([Level 3 - Level 2 Area]),[Level 2 - Level 1 Area],[Level 3 - Level 2 Area])
as [Level 3 - Level 2 Area]
,if(isnull([Level 3 - Name]),[Level 2 - Name],[Level 3 - Name])
as [Level 3 - Name]
,if(isnull([Level 3 - Area]),[Level 2 - Area],[Level 3 - Area])
as [Level 3 - Area]
,if(isnull([Level 3 - Target]),[Level 2 - Target],[Level 3 - Target])
as [Level 3 - Target]
,if(isnull([Level 3 - Acc#]),[Level 2 - Acc#],[Level 3 - Acc#])
as [Level 3 - Acc#]
,if(isnull([Tier3]),[Tier2],[Tier3]) as [Tier3]
;
LOAD
[%AccountArea]
,[%Reduction]
,[Level 1 - Name]
,[Level 1 - Area]
,[Level 1 - Target]
,[Level 1 - Acc#]
,[Tier1]
,if(isnull([Level 2 - Level 1 Area]),[Level 1 - Area],[Level 2 - Level 1 Area])
as [Level 2 - Level 1 Area]
,if(isnull([Level 2 - Name]),[Level 1 - Name],[Level 2 - Name])
as [Level 2 - Name]
,if(isnull([Level 2 - Area]),[Level 1 - Area],[Level 2 - Area])
as [Level 2 - Area]
,if(isnull([Level 2 - Target]),[Level 1 - Target],[Level 2 - Target])
as [Level 2 - Target]
,if(isnull([Level 2 - Acc#]),[Level 1 - Acc#],[Level 2 - Acc#])
as [Level 2 - Acc#]
,if(isnull([Tier2]),[Tier1],[Tier2]) as [Tier2]
,[Level 3 - Level 2 Area]
,[Level 3 - Name]
,[Level 3 - Area]
,[Level 3 - Target]
,[Level 3 - Acc#]
,[Tier3]
RESIDENT
[tempSalesHierarchy]
;
Thanks
Oli
