Maximum Date/Timestamp

TIMAI2 · September 21, 2020, 4:44pm

This formula will return the Lat/Lon (if placed in each column) for the query output:

=ARRAYFORMULA(ARRAYFORMULA(IFERROR(VLOOKUP($G$11:$G&$H$11:$H, {$A$2:$A&$B$2:$B, $C$2:$D}, 2, 0 ), 0)))

Run the query just on Select B,MAX(A)
then this formula in the column next to the query output. Adjust $G$11:$G&$H$11:$H and the output column number 2 accordingly. For some reason it kicks out an extra column of 0's for me

Anup_Ludra · September 21, 2020, 4:57pm

I was also seeing this

Anup_Ludra · September 21, 2020, 5:08pm

I put this formula nd adjusted the Columns and Rows, I got JUST THE LAT Values

TIMAI2 · September 21, 2020, 5:11pm

You need to put the formula again in the next column for the Lon values, and as said, change the number 2 to a 3.

Anup_Ludra · September 21, 2020, 5:11pm

GOT IT !!

I changed C to D
Not 2 to 3
?????

ABG · September 21, 2020, 5:52pm

TimAI2 won this one, using Google Sheets.

For the record, here's a working solution using a csv download and a bunch of block procedures:

Sample Data - Sheet1.csv (228 Bytes) Sheets_Latest_By_Name.aia (7.7 KB)
(I used my own data at https://docs.google.com/spreadsheets/d/1IylVhDBsp-ZbxQTDsuSgk1q9F55aBIVpOj_QDVDgn4g/edit?usp=sharing
because I have no GPS data.)
P.S. Credits to TimAI2 for the getRange function

Anup_Ludra · September 21, 2020, 6:01pm

Thank you vey much
It was an awesome learning experience.

When user position reported

Final refining is yet to be done. Will do that subsequently.
Thank you once again @TIMAI2 and @ABG
Timer based refresh from Gogle Sheet. When fresh data received, map position updated

TIMAI2 · September 21, 2020, 10:59pm

Got it all into one formula!!

=query(A2:D,"select max(A),B,C,D group by B,C,D order by max(A) desc Limit "&counta(unique($B$2:$B))&" LABEL max(A) ''")

Using group by for the lat/lon columns
Include a formula that counts the number of unique names in Column B to limit the return

Anup_Ludra · September 22, 2020, 4:45am

AWESOME !!!!
Tx a ton !
Truely Appreciate your passion !

Anup_Ludra · September 22, 2020, 6:08pm

I been trying this snce morning. I works sometimes, and sometimes it dosent.
I havent been able to gauge as to why this is happeneing !!

TIMAI2 · September 22, 2020, 6:25pm

That first image just looks so wrong!

I will have another look at the formula....

Anup_Ludra · September 22, 2020, 6:27pm

If there is a time gap in the data being uploaded, then ths error comes.
Image 2 was earlier. Then there was a time gap

I am uploading test data to refine the app. But after a time gap, this error is recurring.

As of NOW !

TIMAI2 · September 22, 2020, 8:24pm

For the moment, It is back to the previous query formula and the arrayFormulas for the vlookup.

I will post a question on SO, as I cannot find anything similar out there.

Anup_Ludra · September 22, 2020, 8:38pm

I have done just that. To make the app function.

TIMAI2 · September 22, 2020, 8:45pm

Good man

Anup_Ludra · September 22, 2020, 8:46pm

Now adding Geofence alarm

TIMAI2 · September 22, 2020, 9:09pm

I have had a think.....

Try this formula (a bit long, but it appears to work)

=sort(filter(A1:D,match(A1:A,query(A2:D,"Select max(A) GROUP BY B ORDER BY max(A) desc LABEL max(A) ''" ),0),match(B1:B,query(query(A2:D,"Select B, max(A) GROUP BY B ORDER BY max(A) desc LABEL max(A) ''" ),"Select Col1"),0)),1,FALSE)

Anup_Ludra · September 22, 2020, 9:20pm

Trying it out.

Anup_Ludra · September 22, 2020, 9:25pm

Seems Good to Go !! (As of Now)
Tx for ur efforts

Will test out exhaustively with breaks, to induce time gaps, and revert.

Anup_Ludra · September 22, 2020, 10:13pm

I have reverted back to the VLOOKUP queries