My data table has a structure like this
ID MinionID ParentID Title
1 I'm the boss
2 1 I'm a minion
3 1 Banana!?
In my working sheet ID is selected, using
GetFieldSelections(ID) I get the string '1' (or a comma separated string list if more than one ID is selected)
What I want to do now is find all entries that have the same ParentID as the selected ID. Sounds trivial or so I thought. 4 hours later and still not closer finding the correct function, I'll humbly ask for help. Any idea how to do that?
I have two tables on my Sheet (picture was done with Excel, imagine it would be two tables in Qlik Sense).
When an entry in the Boss Table is selected (Filter active) then the Minion Table should display the items that have that Boss as a parent. I'm looking for the field function for the Minion Table that finds the entries in the ParentID column.
SOURCE: LOAD * INLINE [ ID, MinionID, ParentID, Title 1, , , I'm the boss , 2, 1, I'm a minion , 3, 1, Banana!? ]; [Boss Table]: LOAD ID AS BossId ,Title AS BossTitle RESIDENT SOURCE WHERE LEN(ID) > 0 ; [Minion Table]: LOAD ParentID AS BossId ,MinionID AS MinionId ,Title AS MinionTitle RESIDENT SOURCE WHERE LEN(MinionID) > 0 ; DROP TABLE SOURCE ;
Also take a look at hierarchies, since the source table contains hierarchies.
I hope it won't come to this, that a further split of my data table is necessary. Hierarchies won't work on the original data table this problem is derived from, there are to many restrictions on how to use them.
My current solution is this
= if( GetFieldSelections(ID) = [ParentId], [MinionID], Null())
but it doesn't work. If I use this
= if( '1' = [ParentId], [MinionID], Null())
it works. Which is of course absolutely useless with dynamic data selection use cases. I guess that Qlik Sense is having problems with my mix of primary keys (ID, MinionID) and non primary keys (ParentId).
Hierarchy(MinionID, ParentID, Title) LOAD * ; LOAD If(Len(MinionID) = 0, ID, MinionID) AS MinionID , If(Len(ParentID) > 0, ParentID) AS ParentID , Title ; LOAD * INLINE [ ID, MinionID, ParentID, Title 1, , , I'm the boss , 2, 1, I'm a minion , 3, 1, Banana!? ];
Table after historization