Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
Dan17
Contributor II
Contributor II

Find value in column

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?

 

Labels (1)
6 Replies
abhijith28
Creator II
Creator II

can you provide a example with a expected output

Dan17
Contributor II
Contributor II
Author

Dan17_1-1663661691568.png

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. 

Ruhulessin
Partner - Contributor III
Partner - Contributor III

Hi @Dan17,

Try this:

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.

-Ruben

I have never done it, so I think I can.
Dan17
Contributor II
Contributor II
Author

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).  

 

Ruhulessin
Partner - Contributor III
Partner - Contributor III

Try this

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

Ruhulessin_1-1663768661613.png

Visulizations frontend

Ruhulessin_2-1663768676892.png

 

 

I have never done it, so I think I can.
Dan17
Contributor II
Contributor II
Author

Once again this problem is not about loading the table, this is about finding entries in an existing already loaded table that has a specific data layout.