topic LookUp in chart in App Development

LookUp in chart

Vojta — Mon, 12 Jun 2023 11:47:07 GMT

Hi , I have a following table:

Record ID	State	Parent ID
123	Work in Progress	234
234	Open	null

and I would need to display for each record Parent State, so it would look like that:

Record ID	State	Parent ID	Parent State
123	Work in Progress	234	Open
234	Open	null	null

There is a LookUp function, but it is only a script function and I am not allowed to use it (limited access rights). I tried some workarounds, but they are not working. If you have any idea, I would be grateful.

Thanks

Re: LookUp in chart

Chanty4u — Mon, 12 Jun 2023 18:16:09 GMT

Try this

Table:

LOAD

[Record ID],

[State],

[Parent ID]

;

LEFT JOIN (Table)

LOAD

[Parent ID] as [Record ID],

[State] as [Parent State]

RESIDENT Table

WHERE NOT IsNull([Parent ID]);

Re: LookUp in chart

Vojta — Tue, 13 Jun 2023 05:36:15 GMT

Hi,

thanks for the response, but the app is locked and I am not able to do anything in the data load editor.

Re: LookUp in chart

sidhiq91 — Tue, 13 Jun 2023 06:34:11 GMT

@Vojta YOu can create a calculated Dimension like below in your table:

=if([Parent ID]='234','Open',null())

Re: LookUp in chart

Vojta — Tue, 13 Jun 2023 06:43:54 GMT

@sidhiq91 Yes, I can, but I have a lot of rows and the number of rows is increasing each month, so I would need a dynamic solution.

Re: LookUp in chart

tresB — Tue, 13 Jun 2023 07:19:46 GMT

It's difficult to do in the UI without making changes to the script. You can use pick(match(... along with dollar sign expression like below. The solution would be a bit maintenance heavy though. In summary, the expression is evaluated in such a way that it produces separate expressions for each row of the chart. I have added one extra line in sample data.

Pick(Match([Record ID],$(=Concat(Distinct chr(39)&[Record ID]&chr(39), ',',[Record ID]))),
$(=Concat('Only(Total{<[Record ID]={'&[Parent ID]&'}>} State)', ',',[Record ID]))
)

Note: This is one of those qlik solutions that I don't encourage people to practise, instead the solution for such requirements should be taken to script. And also becasue - people ask to explain the expression and how it works, which I don't like (i.e. - explaining lengthy things on community.) 😋

Re: LookUp in chart

Vojta — Wed, 14 Jun 2023 07:02:15 GMT

Thanks, @tresB , I understand the concept and it would probably work, but as you mentioned, it is maintenance heavy and Qlick always freeze, when I try to use this expression.