2 Replies Latest reply: Feb 9, 2016 11:21 AM by Sathish G RSS

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

    Oliver Annells

      Hi


      I have a hierarchy table which has 3 levels. 

      Level 1Level 2Level 3
      Manager 1User 1User 4
      Manager 1User 2
      Manager 2
      Manager 3User 3User 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 1Level 2Level 3
      Manager 1User 1User 4
      Manager 1User 1User 1
      Manager 2Manager 2Manager 2
      Manager 3User 3User 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

       

        • Re: filling null() fields from another field in the same table
          Srikanth P

          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]

          • Re: filling null() fields from another field in the same table
            Sathish G

            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