LookUp in chart

[Vojta]

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

[Chanty4u]

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]);

[Vojta]

Hi,

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

[sidhiq91]

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

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

[Vojta]

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

[tresesco · Accepted solution]

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

[Vojta]

Thanks, @tresesco  , 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.