Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please try like below with Pick function:
Pick( IF(LEN(TRIM([Level 3])),0,1) + IF(LEN(TRIM([Level 2])),0,1) + IF(LEN(TRIM([Level 1])),0,1) + 1 ,
'Default Value if Level 1 don't have any value' ,
[Level 1] ,
[Level 2],
[Level 3]
) AS [Level 3] ,
Pick( IF(LEN(TRIM([Level 2])),0,1) + IF(LEN(TRIM([Level 1])),0,1) + 1 ,
'Default Value if Level 1 don't have any value' ,
[Level 1] ,
[Level 2]
) AS [Level 2],
Pick( IF(LEN(TRIM([Level 1])),0,1) + 1 ,
'Default Value if Level 1 don't have any value' ,
[Level 1]
) AS [Level 1]
HI,
Try this,
load
Level1,
if(isnull(Level2),Level1,Level2 as Level2,
if(isnull(Level3),if(isnull(Level2),Level1,Level2,Level3)) as Level3
from tablename
-Sathish