Select random rows from data source using weightings


I’ve just started with Thunkable and I’ve got to the point where the next thing I’m trying appears to be too complex for a simple solution using obvious blocks. I’ve searched and searched, read, done tutorials, watched videos but I can’t find anything to apply to my situation.

I have a list of exercises in a google sheets worksheet…

I am able to filter out exercises for a specific category successfully.

But this returns too many exercises.

I want to return a subset, e.g. 8 exercises for a category (when I select a category in the ‘main screen’. What I want to do is randomize these exercises taking the weighting (preference) into account so higher weighted exercises are returned more frequently than lower weighted ones.

Can anyone suggest how to go about this please?

Thanks, Matt

I would approach this in a different way. Instead of weighting individual exercises and trying to randomize the selection of higher weighted ones, I would weight an entire set of exercises. So you’d have a set of rows (or a separate data source) weighted “10”, another set weighted “20”, etc. The randomizing would simply pick a random set (weighted) and then randomize a selection (non-weighted) from that set.

Hi @tatiang,

Thank you for your response. I can’t quite get my head around the concept. Are you saying that I create a number of sets/selections of exercises from different categories and apply a weighting to each set? These would then be selected on a preference basis, with the remainder of the final selection being made up from a non-weighted set? I think I may be over-engineering it and confusing myself!

Cheers, Matt

Not quite.

Let’s take a slightly simplified version of your data:

Squat 50
Bridge 10
Hydrant 50
Curl 10
Raise 10

We would create a column or spreadsheet for each weight. It could even be a list variable if you don’t need it to live in the cloud. But I’m guessing you do. Like this:

50: squat, hydrant
10: bridge, curl, raise

When you want to select an exercise, you use probability to generate a random set (either 50 or 10). So that would look something like if random 1 to 100 ≤ 10 then then choose “10” set… else if random 1 to 100 ≤ 50 then choose set “50”. So there’s a low chance (1 to 10) of “10” coming up and a higher chance (11 to 50) of “50” coming up. Obviously the weightings aren’t quite right because that’s a 10% chance of “10” and a 40% chance of “50”. My math is a little off. And I haven’t included all of the possible weights from 0 to 100 but you could do that.

After you choose a set this way, then you just select a row/list item at random from that set. There’s no math involved there. It’s just using a random block and probably the [length of list] or [number of rows] block to pick the specific exercise.

So an example would be that the random integer you generate is 16 and therefore set “50” is chosen. And then a random integer from 1 to 2 (because I only listed two items above) generates 1. So the exercise chosen for the user is “Squat.”

1 Like

i think your app will probably ask questions like these:
how many of the 6 Abs exercises do you want to do? how many of the 13 Glutes exercises do you want to do? etc. so you may end up with a workout plan of 3 Abs, 6 Glutes and 2 Shoulder, etc.

this is how you would process Abs and select 3 from the list.
create a list of encoded exercises (column E) as shown below:
(to compute code at E2, use this formula d2*1000+a2)

then sort the list and you get this

the exercise with the highest weights (#20,#21 end up in front and the lowest at (#22) the end.
at this point you can select the top 3 in the list and you’ve got your 3 Abs exercises. However, you will always end up with these 3 and will never do the last 2!

the solution?
before you select, pick a random number from 1 to 100. if you get 50 or under, get the first entry and REMOVE it from list.
Otherwise, get a random number from1 to 6 (current length of selections) and get that position from the list and also REMOVE it from list.

you have your 1st of the 3 items you want and you have one less item from your selection list.
you’re ready to apply the same logic to the remaining list until you get 3.

here’s a sample sequence

your list of selected codes for Abs is 50021, 50020, 70022 - get the last 3 digits (remainder after dividing by 1000) and you get exercise id 21, 20, 22!

(Note: you can change the threshold to a higher value, say 70, if you want better chances to pick the highly weighted items than a simple random choice. )

will this work for you?

Thanks for your responses. After a fair bit of head scratching I’ve realised that Thunkable isn’t suitable. It seems to me that it’s only suitable for basic stuff, it doesn’t seem to have the capability to query dynamically amongst other things.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.