Implement RANK() OVER (PARTITION BY ORDER BY) as virtual column

[leeca]

Hello,

Is there any way to implement the following SQL Query in AppSheet as a virtual column:

RANK() OVER (
    PARTITION BY [Date]
    ORDER BY [Created_At]
) AS Sequence

[Joseph_Seddik · Accepted solution]

OK I understand, thank you!

Well, the Rank() function might not be an optimal solution in case for example a customer returns again after being served the first time. The second time, and later times, he would get the same sequence number as the first time. 

If you would like to have a sequence that would reset on a daily basis, then a better solution is to add a "Sequence" column, normal not virtual, and put this expression in its App Formula:

COUNT( FILTER("tableName", TODAY() = [Date]) ) + 1

Even better, since you are already using a daily slice, the expression should be: 

COUNT( FILTER("sliceName", TRUE) ) + 1

________

Rank() is a costly, capricious futility 😀

[Joseph_Seddik]

Yes, there are ways depending on what you’d like to achieve. 

Literal code transcription is generally not a good practice, better explain in plain language what is your requirement for which you’d need a rank. 

[leeca]

Ok, say I have a single table, Customers, with the following columns: [Date], [Customer Name], [Created At]. For brevity, I have 6 customers coming in over 2 days (3 customers per day). I would like to rank these customers on each day, by the order that they came in at.

Date	Customer Name	Created At
06/03/2022	Customer C	06/03/2022 08:32:56 AM
06/03/2022	Customer B	06/03/2022 15:32:56 PM
06/03/2022	Customer A	06/03/2022 11:32:56 AM
07/03/2022	Customer A	07/03/2022 09:45:20 AM
07/03/2022	Customer B	07/03/2022 17:05:46 PM
07/03/2022	Customer C	07/03/2022 12:22:03 PM

I would like to add a virtual column named "Sequence", showing the rank of each customer by the time the values were created at on each day.

Sequence	Date	Customer Name	Created At
1	06/03/2022	Customer C	06/03/2022 08:32:56 AM
3	06/03/2022	Customer B	06/03/2022 15:32:56 PM
2	06/03/2022	Customer A	06/03/2022 11:32:56 AM
1	07/03/2022	Customer A	07/03/2022 09:45:20 AM
3	07/03/2022	Customer B	07/03/2022 17:05:46 PM
2	07/03/2022	Customer C	07/03/2022 12:22:03 PM

[Joseph_Seddik]

Thank you. I already understood this from the expression in your original post.

My question is: what do you need the rank for? What would you do with the rank afterwards? Would you please explain? 

[leeca]

I am using SQL Server 2019 as the backend to AppSheet.

The rank would be used in a slice, filtered using [Date] = TODAY(), so I can see the order in which the customers came in. The UX for this would be using Table type, with name: Detail View.

This is to:

1. Display to the user so they can service the customers in that order

2. Display to the user the total number of customers that came today by scrolling to the last row

AFAIK, there is no option to display a single metric in AppSheet, except to use a SQL query to summarize and use a deck / card view to display a slice filtered by today.

E.g. to obtain Customer_Count_By_Day as a single metric view:

CREATE VIEW Customer_Count_By_Day AS

SELECT 

    Date,
    COUNT(Customers) AS Customer_Count
FROM customers
GROUP BY Date

Add this query view as a Table to AppSheet, create a filtered slice, display to user in UX as card / deck (Name: Metric View) and combine that in a Dashboard view as:

1. Metric View

2. Detail View

Where I am stuck at now is to implement the RANK() function in AppSheet, so the user can scroll through the list of customers, ordered by the time at which they were created, for the purposes outlined above.

[Suvrutt_Gurjar]

Could you update how [Created At] column is populated?

[leeca]

[Created At], "DateTime" data type, Initial Formula: NOW()

[Suvrutt_Gurjar]

Thank you. Maybe I still missed some point , however

1. Generally AppSheet adds rows in sequential order of timetamp. Any specific reason the record of later timestamp will be stored earlier ?

2.  Are you not able to use sort by [Created at] on the table view of  the slice you will create with [Created at]=TODAY(), which will give the sort order in [Created at]

[Joseph_Seddik]

Thank you..

Fortunately for this purpose you don't need a virtual column with formulas which is always better to avoid.

You'll just create the slice normally and link it to a view. In the view you can specify the sorting order of your linked table/slice based on any column, and in your case it will be the "Created_At" column. Also in the view, you don't even need to show the "Created_At" column even if you are using this column for sorting the output. 

The user will see customers, as required, in the order they had arrived in.

Also,

In the view, in addition to sorting, you CAN group by any column and automatically display a group metric like Count, Sum, Average, Min and Max. You should just have the required columns to group by in your table. So if you'd like to group by date on a daily basis, you should have a column "Date" with an initial value of TODAY(). 

Please read the guide below and tell me if you have any doubts. 

Views: The Essentials | AppSheet Help Center 

_______

Personal opinion: Rank function in SQL is just an expensive futility. 

[leeca]

Thanks to both for the input.

However, the display of the sequence matters to the user, as we need to refer to the customer by the order they came in at today, e.g. Customer 15 needs to be serviced.

While that can be achieved using Sort By in UX and hiding the [Created At] column, the user would not be able to see or refer to the [Sequence No] that the customers came in at for today.

To try and illustrate this with a story, suppose the user is a bank officer sitting in the branch. 10 customers came in and were registered using [Created At], clustered around 8:45AM with a tight range around the seconds.

Each of the customers are given a printed ticket without a registration number [Sequence No].

With [Created At] timestamp as the only information available, the customers would be arguing, "Hey, I came 2 seconds earlier, why am I not being serviced first?" and the user would need to compare timestamps to resolve the conflict.

[Joseph_Seddik · Accepted solution]

OK I understand, thank you!

Well, the Rank() function might not be an optimal solution in case for example a customer returns again after being served the first time. The second time, and later times, he would get the same sequence number as the first time. 

If you would like to have a sequence that would reset on a daily basis, then a better solution is to add a "Sequence" column, normal not virtual, and put this expression in its App Formula:

COUNT( FILTER("tableName", TODAY() = [Date]) ) + 1

Even better, since you are already using a daily slice, the expression should be: 

COUNT( FILTER("sliceName", TRUE) ) + 1

________

Rank() is a costly, capricious futility 😀

[leeca]

Masterful .. thank you for the solution 😀

[leeca]

I had some further thought about this, - to prevent the sequence from resetting on a daily basis (so that the user can trace back the sequence no. of customers on any given day):

COUNT( FILTER("tableName", [Date Selected] = [Date]) ) + 1

Where [Date Selected] could be stored as a column in Users table (so each logged in User can have their own filter), and then slice the Customers table by [Date Selected].

I will be giving this a go and see if it works.

[leeca]

The solution worked for resetting a sequence on a daily basis, but not for preserving the sequence on any selected day.

I tried to solve the problem as below:

Tables:

1. Customer

2. Users

Customer contains the columns:

[Date], DATETIME, Formula = NOW()

[Customer Name], TEXT

[Created At], DATETIME, Formula = NOW()

[Sequence], NUMBER, Formula = 

COUNT(FILTER("sliceName".[keyName], DATE([Date]) = ANY(Users[date_filter]))) + 1

Users contains the columns:

[Email], TEXT

[User Name], TEXT

[Date Filter], DATE

Dashboard filter created as a slice from Users (so that each currently logged in user has their own [Date Filter] column:

Customers slice created as below to display rows filtered by [Date]:

I had expected the Sequence column to respond to changes in the [date_filter]. However, Sequence only updates on adding new entries - when a user selects a previous date that is not today, the Sequence shows as blank.

I have also tried to toggle the "Reset on Edit?"  option on the Sequence column.

[Suvrutt_Gurjar]

Gnerally such sequence numbering with AppSheet virtual column will be sync time expensive because it will be a multirow expression.

Anyway, you may wish to try something like below for the sequence number expression in a number type VC

If the table's key is fixed lenghth ( 8 character ) such as UNIQUEID(), please use an expression something like

FLOOR(FIND([Key Column], SliceName[Key Column])/11)+1

If the key column length is uneven, please try an expression something like 

COUNT(SPLIT(LEFT(SliceName[Key Column],FIND([Key Column],SliceName[Key Column])),” , "))

[leeca]

@Suvrutt_Gurjar I have tried using your expression, but the sequence order appears to be  reversed, i.e. Sequence [1, 2, 3] appears as Sequence [3, 2, 1]

Yes I am using UNIQUEID() as key and App Formula. 

[Suvrutt_Gurjar]

@Joseph_Seddik : As it happens a few times with this community portal, I did not see your latest reply when I was typing in my reply. The previous portal used to to show if someone else  is typing. In such cases, one could wait, especially if the other person is already responding to the thread. 

In this portal, unless I manually refresh, I do not see latest posts, or am i missing some setting?

[Suvrutt_Gurjar]

Hi @Joseph_Seddik : As it happens a few times with this community portal, I did not see your latest reply when I was typing in my reply. The previous portal used to to show if someone else  is typing. In such cases, one could wait, especially if the other person is already responding to the thread. 

In this portal, unless I manually refresh, I do not see latest posts, or am i missing some setting?

[Joseph_Seddik]

No my friend you are not missing anything. It happened with me a lot of times ever since we moved to this pessimal portal, and it is very annoying 😔

[Suvrutt_Gurjar · Accepted solution]

Sorry, should have included ORDERBY(), even though even without that typically list gets created in sequence. Anyway please try 

FLOOR(FIND([Key Column],    ORDERBY(SliceName[Key Column],[Date], FALSE ))/11)+1

Edit: Added one missing parenthesis

[Suvrutt_Gurjar]

This was an interesting discussion thread that outlined two approaches of sequence numbers with real column and virtual column. Just for any future readers of the thread, the better points and challenges between a real column and a VC approach, as per my understanding.

Real column will calculate the sequence number when a row is being added. So it will be better in terms of overall sync time of the app as it will not be recalculated on each sync. However, if multiple user are adding the rows, sequence number may be duplicated or if a few rows are deleted after adding, the sequence number will not be recalculated for the following rows unless one uses actions or physically redits those rows. 

With VC, the sequence number will dynamically adjust itself ( of course on resync of the user's device) when rows are added or deleted. However if there are many hundreds of rows in the slice on a day, it will impact the sync time. It may not so much impact the sync time for a few 10s or few 100s of rows. Also with VC the "real" storage of sequence in the adatabase is not there, which could be needed in certain cases.

[leeca]

@Suvrutt_Gurjar Yes you are right, I have tested your formula and the sync time has made the app unusable, data contains about 10k rows

[Joseph_Seddik]

Very interesting. Thanks my friend!

[Suvrutt_Gurjar]

Oh okay, thank you. Then VC is obviously to be ruled out. May we know if in spite of daily slice, the data is so large?

[leeca]

The data covers about 504 days with an average of 20 entries per day. I think the VC is a nice idea for what I would like to achieve i.e. preserve sequence for any given day, and dynamically update on refresh. The sync time has made the app unusable though.

[leeca]

@Suvrutt_Gurjar I managed to get it to work:

FLOOR(
  FIND(
    [keyName], 
    ORDERBY(sliceName[keyName], [Created At Time])
  ) / 11
) + 1

I had to use [Created At Time] = TIME([Created At]) to extract the time from [Created At] column.

When a user filters by date, the slice displays the available rows for the selected date, and recomputes the sequence, ordering by time.

After this tweak, there are no performance issues for the VC solution as well, many thanks for your help.

One caveat here - in the UX View, using this VC, all sequence values initially show as "1", until the user refreshes the app.

[Suvrutt_Gurjar]

Oh got it. Thank you for the updates. Good to know it works the way you want. 

[Steve]

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Serial-Numbers-If-You-Must/m-p/286300