Based on Level of a hierarchy, display a specific column

[MrAndrews]

Hi

I have a drill in a qlik sense app -  I am able to identify what level i am at in the drill (=GetObjectField(0)), but based on that i want to be able to output a specific field.

I have tried all the options i can think of!

=if(GetObjectField(0) = 'Hierarchy2' , 'Correct level', 'Not at this level') - this returns  'Correct level'

=if(GetObjectField(0) = 'Hierarchy2' , [H2_DriverName], 'Not at this level')- this returns  'Not at this level' 

So in the example below the required output would be whatever is in H2_DriverName.  If the user drills further it would go to H3, H4 etc.

Cheers!

Hierarchy2	=GetObjectField(0)	Required Output	H2_DriverName	H3_DriverName	H4_DriverName
Com AA	Hierarchy2	Oblong	Oblong	Rectangle	Cheese
GR BB	Hierarchy2	Circle	Circle	Banana	Lemon

[sunny_talwar]

Would you be able to share a sample to check this out

[MrAndrews]

Sure - it's in Qlik Server, so I can't get to it, but i mocked this up - it has the same behaviour.  

Like I said - I want to output the values in the column, but I can't work out how to do that!

So if the user hasn't drilled yet, and is it Hierarchy2, then they should see the outputed values from H2_DriverName

Much  appreciated.

[sunny_talwar · Accepted solution]

Since this is a dimension... try this

=if(GetObjectField(0) = 'Hierarchy2', Aggr(H2_DriverName, H2_DriverName), 'Not H2')

[MrAndrews]

magnificent! So, as I am using Aggr(dimension, dimension) it brings back the single option for that field name?

[MrAndrews]

Actually, that does cause issues when I embed some more IF statements:
=if(GetObjectField(0) = 'Hierarchy2' , Aggr([H2_DriverName], [H2_DriverName]) ,
if(GetObjectField(0) = 'Hierarchy3' , Aggr([H3_DriverName], [H3_DriverName]) ,
if(GetObjectField(0) = 'Hierarchy4' , Aggr([H4_DriverName], [H4_DriverName])
)
)
)

[MrAndrews]

The above creates blank records at each level!

[sunny_talwar]

Not entirely sure why it does that... but can you check this

=if(GetObjectField(0) = 'Hierarchy2' , Aggr(NODISTINCT [H2_DriverName], [H2_DriverName]) ,
 if(GetObjectField(0) = 'Hierarchy3' , Aggr(NODISTINCT [H3_DriverName], [H3_DriverName]) ,
 if(GetObjectField(0) = 'Hierarchy4' , Aggr(NODISTINCT [H4_DriverName], [H4_DriverName]))))

[MrAndrews]

That sorts it. Thanks for helping!

[sunny_talwar]

Happy to help 🙂