topic Re: Find value in column in App Development

Find value in column

Dan17 — Mon, 19 Sep 2022 14:12:51 GMT

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?

Re: Find value in column

abhijith28 — Mon, 19 Sep 2022 14:21:20 GMT

can you provide a example with a expected output

Re: Find value in column

Dan17 — Tue, 20 Sep 2022 08:24:56 GMT

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.

Re: Find value in column

Ruhulessin — Tue, 20 Sep 2022 11:27:11 GMT

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

Re: Find value in column

Dan17 — Tue, 20 Sep 2022 13:10:05 GMT

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

Re: Find value in column

Ruhulessin — Wed, 21 Sep 2022 13:58:46 GMT

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

Visulizations frontend

Re: Find value in column

Dan17 — Wed, 21 Sep 2022 14:05:07 GMT

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.