Lookup a value based on a chart expression

[scroggi323]

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

[PrashantSangle]

Can you provide sample data with expected output?

[Or]

Sounds like you might be looking for IntervalMatch() -  https://community.qlik.com/t5/Design/Using-IntervalMatch/ba-p/1475510

[scroggi323]

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

[scroggi323]

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. 

[scroggi323]

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