Hi,
I want to create an expression that assigns a random position based on the total number of subscribers in a competition with no duplicates.
The formula I’m using (virtual column) is the following:
RANDBETWEEN(1,COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID])))
It assigns the position but almost always if I have let’s say 5 subscribers in a competition, some of them have the same position.
Is there any way to assign a different position to each subscriber?
Thanks!
@juanpa
As already opposed by the function itself, RANDBETWEEN function produces random numbers between a minimum range (i.e. in your example it’s 1) and a maximum range (i.e. in your example it’s 5). As the interval between the min. and the max. range is too close to each other, it’s very likely that it will produce doubles. Provided you can explain how important this number for you, how many digits max. you want to generate/see, I can propose a bit more robust solutions. For example:
NUMBER(
CONCATENATE(
RANDEBETWEEN(1000, 9999),
RANDBETWEEN(1,COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID])))
)
)
@LeventK thanks for answering.
I tried the expression but I get the same problem. Here’s the screenshot:
Now, about the number. Yes, the number is very important because as I mentioned, I’m building a competition app so each subscriber should see his position to know when is his turn.
This is what I get now:
And this is what I need:
This example has 5 subscribers but it could be 60. So I need to assign each one a position between 1 to 5 or 1 to 60 depending on the number of subscribers in the competition (Trial).
Provided your app has multiple users entering data, then I’m afraid there’s no way to provide a sequential numbering in any order or form as 2 users entering data at the same time have the probability of having the same number.
May I ask how and by whom are those competitor records are created? By competitor themselves or only you?
The competitor is the person who adds records using an app created for them.
@juanpa
This could be one option that I can come up with. @Steve is there a better option or is it possible to shorten the expression do you think?
INDEX(
TOP(
SORT(
{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
FALSE
),
COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
) -
SELECT(
Punctuation[Position],TRUE
),
RANDBETWEEN(
1,
COUNT(
TOP(
SORT(
{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
FALSE
),
COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
) -
SELECT(
Punctuation[Position],TRUE
)
)
)
)
@LeventK I really appreciate your time and help. I tested the expression but it keeps adding duplicates positions (5) . Here’s the screenshot:
…
I’ll keep trying to solve this. I hope @Steve could also help me with this expression …
@juanpa
I’m afraid there isn’t a robust solution with this sequantiality. You may want to read it here as well. You column is not a key but the overall idea is the same.
@juanpa
Can you also try with this?
IF(
COUNT(SELECT(Punctuation[Position],TRUE)) =
COUNT(
TOP(
SORT(
{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
FALSE
),
COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
)
) - 1,
ANY(
TOP(
SORT(
{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
FALSE
),
COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
) -
SELECT(
Punctuation[Position],TRUE
)
),
INDEX(
TOP(
SORT(
{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
FALSE
),
COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
) -
SELECT(
Punctuation[Position],TRUE
),
RANDBETWEEN(
1,
COUNT(
TOP(
SORT(
{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
FALSE
),
COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
) -
SELECT(
Punctuation[Position],TRUE
)
)
)
)
)
It says COUNT function is used incorrectly …
@juanpa
Totally my bad, do apologize. Forgot to enclose the COUNT with SELECT on top. Fixed the expression in my prior post, so please try with copying the new one from there.
@LeventK tested. It keeps adding duplicates:
@juanpa
Can I look into your app for this? If so, please add levent@able3ventures.com as a co-author to your app and also please share the back-end gsheet as well with Edit access. Thnx.
I just shared with you the app and back-end. The table name is punctuation and the name of the virtual column I’m adding the formula is Random position. Please let me know if you need anything else.
I believe there’s a problem with the app sharing. I cannot see the app under my co-authored apps pane. Email received but when I click the Edit link, it opens AppSheet’s Sample App page. Can you please check from Users pane that the email address I have given has editable access to your app definition? Thnx.
@juanpa
I can open the app from the browser only, cannot edit app definition. Please check my role from the Users pane it shall denote that I can edit app definition
Please try now.
@juanpa I can access now. Shall I be looking to Punctuation table and [Starting Position] column? Please guide me.
Punctuation table [Random position] at the end
