Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Vojta
Contributor II
Contributor II

LookUp in chart

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
Labels (1)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

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.

tresesco_0-1686640352412.png

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

View solution in original post

6 Replies
Chanty4u
MVP
MVP

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
Contributor II
Contributor II
Author

Hi,

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

sidhiq91
Specialist II
Specialist II

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

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

sidhiq91_0-1686638043282.png

 

Vojta
Contributor II
Contributor II
Author

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

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.

tresesco_0-1686640352412.png

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
Contributor II
Contributor II
Author

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.