filling null() fields from another field in the same table

[#1]

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

[#2]

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]

[sathishkumar_go]

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