How to select highest score and LOOKUP

Hi there,
Am new to Paperform.
Need some help.
I am trying to create a personality assessment.

  1. There are 9 categories each with a calculated score. How do I write the calculation so that the category with the highest score is selected and the person who fills it up is directed to a specific page that corresponds to that score?

  2. What is an alternative for the excel function LOOKUP in Paperform. Because the meaning of LOOKUP in Paperform is not the same as the LOOKUP function in excel.

Thanks so much.

Hey there.

1. Category corresponding to max score

There are a couple of ways to do this, but I find the most intuitive way is to set up two arrays:

  • a category array
  • a scores array

We’re going to want to these arrays to be of equal length and correspond to each other by position. That is, the first item in the category array should logically connect to the first item in the scores array. Explained differently, the first score belongs to the first category.

Building the categories array

For the category array, it’s pretty straightforward: just name your categories whatever you want. This is what you’ll key in on for your pages (in-form, success). For example, in logic for a page, you might check whether the calculation had returned the category “optimism.”

Example categories array

categories = array("category1", "category2", "category3", "category4", "category5");

Building the scores array

For the scores array, you’ll want to calculate the scores in the same calculation. We can’t access the global scores variable and you wouldn’t want to for multiple scores, anyway, since that variable applies to the entire form’s overall score. Additionally, we can’t reference calculations within a calculation, so all of our intermediate values will need to be in a single calculation.

To calculate one of the scores, you might do something like

score1 = if({{ key1 }} == "someValue", 1, 0);

Repeat as necessary.

Once those scores are computed, we just drop them all into an array as well.

Example scores array

scores = array(score1, score2, score3, score4, score5);

You can be much more descriptive with your variables, of course.

Finding the max score

Using the functions match() and max(), we can combine them to find the position (or index) of the max score within the scores array.

Note: This will only find the first occurrence of the max score in the array. If more than one category is associated with the max score, it will get the index of the first occurrence and ignore the rest. This could potentially be worked around with a bit more complexity, but could get messy.

Example

max_score_index = match(scores, max(scores));

Grabbing the category associated with the max score

We’ve now completed the following steps:

  • computed our scores
  • built our arrays
  • found the max score
  • determined the index of the max score in our scores array

Now, all that’s left is to grab the category associated with that score. Since we have the index and we made sure our arrays were a 1:1 mapping, logically, we can just get() the category by its index.

Example

get(categories, max_score_index);

Our calculation in full

categories = array("category1", "category2", "category3", "category4", "category5");

scores = array(1, 5, 2, 3, 4);
max_score_index = match(scores, max(scores));

get(categories, max_score_index);

This calculation returns the category. So, in logic elsewhere, you’ll want to check if the category is the desired category to present the correct page.

2. Equivalent to Excel’s LOOKUP()

We don’t have a lookup() at all. Rather, it’s a category that contains several functions that may be helpful in looking up data. The closest equivalent I can think of would be similar to what we did above.

Hope this helps!

Hi Danny,

Thanks for your comprehensive answer :smiley:

Will try it out.

Appreciate it

1 Like