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

Tue, 09 Feb 2016 15:39:03 GMT

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

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

Tue, 09 Feb 2016 15:50:28 GMT

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

sathishkumar_go — Tue, 09 Feb 2016 16:21:20 GMT

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

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

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

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

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