How to find place of a number after sorting

[aliasadivet]

Hello,

I'm working on a voting app and my data source is a SP list. I have 2 columns: Score and Place. I need to sort the score column and record a number in place column starting from 1 for the largest score. My final goal is to find first, second and third largest scores. I would appreciate any suggestion to achieve to this goal.

Thanks

[rubin_boer]

hi @aliasadivet 

Consider you have a datasource with columns score and place:

To get the first 3 places in descending order would be like this.

FisrtN(Sort(yourSharePoint, Score, Descending), 3)

Example

Left Gallery  Items = Sort(yourSharePoint, Score, Descending)

Right Gallery Items = FisrtN(Sort(yourSharePoint, Score, Descending), 3)

Column: place | score

Hope it helps,

R

[aliasadivet]

@rubin_boer Thanks for the help.

I need to generate the place number based on the score, i.e. once the scores are sorted, the place for largest score be filled with 1, second largest score with 2 and so on.

Thanks

[rubin_boer]

@aliasadivet so initially your table will only have scores right

[aliasadivet]

@rubin_boer Correct.

[rubin_boer]

@aliasadivet ok here we go:

from sharepoint you only get scores:

Something like this

Scores:

You create a collection where you sort this as previously (above is already sorted): Items = Sort(yourSharePoint, Score, Descending). Notice that the place is empty

The following code will populate the place as you wish

ForAll(colTemp,Patch(colRank, ThisRecord , {place: Max(colRank,place) + 1}))

a temporary collection to patch all the items in your collection. it is important that this only runs once to a specific set. In other words after your score is done you can run this, else you can add some code to set all the places to place to 0 but lets keep it simple for now. add the code to a buttons on select.

Result

The place i snot added.

 to get the top 3 places you can do the following:

Add three labels

label 1 = Index(Sort(yourCollection, score, Descending),1).score

label 2 = Index(Sort(yourCollection, score, Descending),2).score

label 3 = Index(Sort(yourCollection, score, Descending),3).score

Here is demo

Hope it helps,

R 

[aliasadivet]

@rubin_boer I appreciate your time Rubin. I created the collection with sorted scores, and as you said, the place is empty. Now, I need to insert a button and add this code: ForAll(colTemp,Patch(colRank, ThisRecord , {place: Max(colRank,place) + 1})) in its OnSelect. I couldn't figure out where do I need to use the collection with empty place column. Do I need to define colTemp and colRank variables? And using "ThisRecord" in the code, points to what record. Sorry, I'm confused. 

[rubin_boer · Accepted solution]

hi @aliasadivet 

all good lets do this step by step

Step 1: DataSource

I will create one that will replicate the type of data you have but in your case it will be a SharePoint List. 

Add a button to you canvas and add this code to the OnSelect of the button

//creating data for the example. in your case this will be your sharepoint list
//5 entries: 1, 8, 11, 12, 8 with place being empty colRawData will be your SharePoint Data
ClearCollect(colRawData,
    {score:1,place:""},
    {score:8,place:""},
    {score:11,place:""},
    {score:12,place:""},
    {score:8,place:""}
)

Add a datatable and set the data source Items = colRawData

Edit the Fields and add both columns, score and place

Below is a graphic of the datatabel selected of its data source and Fields

Once the fields has been  added the data table ill display the follow: (the score with blank place)

Step 2: Sort data

Lets sort the data in descending order to get the high to low score.

Add another button and set the OnSelect = ClearCollect(colSortedData,Sort(colRawData, score, Descending))

//sort the data to have the first rown the highest score etc...
ClearCollect(
    colSortedData,
    Sort(colRawData, score, Descending)
)

Copy and Paste the datatable in above step and change the datasource to colSortedData. notice the second table is now sorted large to small.

Step 3: Rank the data

we need to run through all the rows and give them a rank, we know it is descending order.

Add another button and add set the OnSelect = ClearCollect(colTemp,colSortedData);
ForAll(colTemp,Patch(colSortedData,ThisRecord,{place:Max(colSortedData,place)+1}))

"this temp collection i neglected to share earlier - sorry for that"

//create the tem[porary collection
ClearCollect(
    colTemp,
    colSortedData
);
ForAll(
    colTemp,
    Patch(
        colSortedData,
        ThisRecord,//thisrecord refers to the current record
        {
            place: Max(
                colSortedData,
                place
            ) + 1
        }
    )
)

the data is ranked

Now lets put it all together in one button

//sort the data to have the first rown the highest score etc...
ClearCollect(
    colSortedData,
    Sort(colRawData /*your sharepoint linst here*/, score, Descending)
);
//create the tem[porary collection
ClearCollect(
    colTemp,
    colSortedData
);
ForAll(
    colTemp,
    Patch(
        colSortedData,
        ThisRecord,//thisrecord refers to the current record
        {
            place: Max(colSortedData,place) + 1
        }
    )
)

Hope that is more explanatory,

R

Ps will you have a situation where there may be more than one score of the same value?

[aliasadivet]

@rubin_boer Rubin, I really appreciate your time and clear explanation! It works exactly the way I wanted.