
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Lookup a value based on a chart expression
I have 2 tables that I am working with the first table has the results of a survey. I need to take the responses and divide that by the total population to get a score. After rounding that score to 2 decimal places, I must then find the percentile from the second table. There are many different ways that people will want to filter this survey data after the calculations, so I need it to be dynamic based on filters. There are 4 different sources for the percentiles as well.
For example,
First table has the columns:
Survey_Year
, Division
, Region
, Location
, category_updated
, specialty
, Employed
, Question_ID
, Question_Desc
, Question_Short
, Responses
, Population_Count
and the second table has
Source
Survey_Year
Question_ID
Score
Percentile
So I have joined the tables on the Question_ID columns and I need to be able to calculate the score from the first table and then match that score to the score in the second table to get the percentile associated with that score, question_id and source
The other wrinkle is if it does not match exactly it needs to find the nearest value and choose that percentile
Thanks for any help you can give

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you provide sample data with expected output?
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sounds like you might be looking for IntervalMatch() - https://community.qlik.com/t5/Design/Using-IntervalMatch/ba-p/1475510

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It would be something similar to this
Source | Survey_Year | Question_ID | Score | Percentile | |||
National | 2023 | 1 | 3.35 | 45 | |||
National | 2023 | 1 | 3.37 | 46 | |||
National | 2023 | 1 | 3.38 | 47 | |||
State | 2023 | 1 | 3.34 | 42 | |||
State | 2023 | 1 | 3.35 | 43 | |||
State | 2023 | 1 | 3.36 | 44 | |||
Question_ID | Survey_Year | Responses | Population | ||||
1 | 2023 | 246 | 86 | ||||
1 | 2023 | 245 | 56 | ||||
1 | 2023 | 244 | 77 | ||||
1 | 2023 | 25 | score after calculation | matching percentile | |||
1 | 2023 | 13 | State | 3.36 | 44 | ||
1 | 2023 | 56 | National | 3.36 | 47 |
There would be more filters built into the charts so that is why it would need to be dynamic

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The problem is I need it to be done in the chart and not in the load script so that I can then filter it on the page itself. The tables being loaded would need to be not aggregated because of the unknown filtering desires of the client.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry forgot to add the other part with more filtering with employed V not employed
Source | Survey_Year | Question_ID | Score | Percentile | |||
National | 2023 | 1 | 3.35 | 45 | |||
National | 2023 | 1 | 3.37 | 46 | |||
National | 2023 | 1 | 3.38 | 47 | |||
State | 2023 | 1 | 3.34 | 42 | |||
State | 2023 | 1 | 3.35 | 43 | |||
State | 2023 | 1 | 3.36 | 44 | |||
Question_ID | Survey_Year | Responses | Population | Employed | |||
1 | 2023 | 246 | 86 | yes | |||
1 | 2023 | 245 | 56 | yes | |||
1 | 2023 | 244 | 77 | yes | Employed | ||
1 | 2023 | 105 | 25 | no | score after calculation | matching percentile | |
1 | 2023 | 106 | 13 | no | State | 3.36 | 44 |
1 | 2023 | 104 | 56 | no | National | 3.36 | 47 |
Not Employed | |||||||
State | 3.35 | 45 | |||||
National | 3.35 | 43 |
