Is it possible to extract a record having Last(Time) ??
I am reading data from Google Sheets. It has mltiple records by many users. I want to read the Last record uploaded by each user.
Group By(Name), Last(Timestamp)
Possibly the easiest way is to format the timestamp to an alphanumeric style date/time in Google Sheets, either directly or with another column, then you can easily sort by that style date once the data is back in the app
Here are sample blocks to get grouped maxima from a table, using text comparison.
Thank u for ur response. Will try this
I tried it, I am sure ur blocks are perfect, but I seem to be going wrong some where ....
My blocks are as under : -
There are multiple users uploaing data to a google sheet. I need to get the Last uploaded data of each user.
I added another column "Time" and the format of the Date/Time I have put as yyyy||MM||dd||hh||mm||ss. So I am getting the data as a numerical 20200921123623. Any subsequent uploading of data will have a value MORE than the previous data. The Min / Max should work with this !
But I seem to be going around in circles.
You could always do a bit more work on google sheets.....
Create a new grid/sheet
Run a query to return the latest time for each user and their data
Call down the query output to your app.
Example:
Thank you, with a little modification, I got the desired result.
Yes, you have to fiddle about with it! This should work:
=query(A2:D10,"select B,max(C),max(D),max(A) group by B order by max(A) desc LABEL max(A) '',max(C) '',max(D) ''")
You shouldn't need to do the date format conversion, the query will work on the original timestamps.
Yes, but SInce it is max(C), max(D) also, in case the new value of Lat is lesser than previous value, it returns PREVIOUS Value of Lat and Long. You see ur query result also. IN case of Bob, the value of Lat returned is or older time stamp. What is surprising is, Timestamp is Max, Lat is Max, but Long is not max. See in the case of Bob.
Try putting max(A) first....
It does order by the timestamp though....
It is doing the order by timestamp, group by name, but ir is also doing max(lat) and max(long) irrespective of the row. instead of taking a DISTINCT record, based on timestamp.
Still takes the max(Lat) and max(Long), irrespective of the Row
Aim is somewhat like this. Get a marker for each user on the map, and WebRead.Get on a Timer, to get updated query result. And refresh the location of the marker. 5 Text boxes getting the Location data of 5 users. And each assigned to a diff marker
Can't see anything wrong?
"group by B order by max(A) desc" will return the latest timestamp entry for each user, what happens to Lat and Lon is not relevant as you get the respective lat/lon for the timestamp.
In case u write
=query(A:D,"select B,C,D,max(A) group by B order by max(A) desc LABEL max(A) '',max(C) '',max(D) ''")
it returns a ERROR
and in case u write
=query(A:D,"select B,max(C),max(D),max(A) group by B order by max(A) desc LABEL max(A) '',max(C) '',max(D) ''")
Then it takes the MAX (LAT) and MAX(LONG), irrespective of the ROW (MAX(Timestamp))
So if a person is moving SOUTH, The Lat Value will keep DECREASING(unless users are in the Southern Hemisphere), but the max(C) will return the max value.
Well, this is working as required for me:
=query(A2:D,"select B,max(A),max(C),max(D) group by B order by max(A) desc LABEL max(A) '',max(C) '',max(D) ''")
you have to aggregate all the columns you want returned, choice of max/min/sum/avg/count
In database management, there ia a function Last(C), this Last function doesnt seem to be working on the Spreadsheet. It works in MS Access though.
I forgot to mention that my procedures require tables (lists of lists) as input.
They will not work with lists of comma delimitted text like you build with your format procedure, since those are only one dimensional lists suitable only for Elements of a ListView or ListPicker.
Regarding your grouping and selection problem in general, I suspect you will have to do this in two phases.
- Collect a table (list of lists) of name (key) and maximum datetime for that name
- Loop through that new name-datetime table and for each row rescan your main table for the row that matches that name and datetime, adding it to a new table of final results.
This requires careful attention to naming your lists and tables to include their shape (list vs table vs Elements) and their function, otherwise your code will stray from your intentions.
If you go the SQL route instead, I suggest researching the SQL HAVING clause in addition to the WHERE clause. (I am unsure as to whether or Google Sheets supports HAVING in its SELECTs.) The HAVING clause is used in conjunction with GROUP BY clauses, as I recall.
I am not sure this would help here.
Here are some more table filter functions from that .aia in my prior post ...
Roger Sir,
Thank you
Will give it a shot tomorrow.
Been at it for the last 2 days.
But will get to the bottom of this !!
If I do this
=query(A:D,"select max(A),B group by B order by max(A) desc LABEL max(A) 'Time'")
I get the Max(Time) of each user in 2 columns.
Now I need the Lat and Long Values.
Can I pull them by another query??
Like
=query(A:D, "select C where A="F2"")
I left this for last, in case Sheets does not allow SQL JOINs:
select_where_equijoin.aia (11.7 KB)
For only a few names, this should be okay.