Last Record from a table of records

[ABBA]

I have a table-Tbl1 with Multiple Rows of data by [Date] for a Column Item
I would like to make a Report or Newtable-Tbl2 with the last know data for that Column Item

I am adding new records every day to this table

Thanks for your help

[Steve]

help.appsheet.com

MAXROW()

Row with greatest value in column

[ABBA]

I understand MaxROW()
I created a slice as Table and tried using MAXROW() function.
The MAXROW() expression does not work with a slice

How do I go about using Maxrow?

[Steve]

A slice is actually easier. Use a row filter expression like this:

(MAX(Tbl1[_ROWNUMBER]) = [_ROWNUMBER])

[ABBA]

I have the Table with ID, Date, O, W,G
It has several rows with same ID, but the Date is different for each row.
The Date can be same for different ID.

I want to get the last entry for each ID
I used the Filter (MAX(Well Tests[_ROWNUMBER]) = [_ROWNUMBER])
It only gives me 1 ID and not for the last date that ID has data, but the last row the table.

I read this MAXROW(“Events”, “Date”, ([Date] < DATE(“1/1/” & YEAR(TODAY()))))…returns the row for the most recent event prior to this year
I want the table only the LATEST ENTRY of ID

Table Name: Well Tests
Column Headings:

|UniqueID|Well Name|API|Section|Date|Oil|Gas|Water|Hours On|%|SITP|SICP|FTP|FCP|24hr Oil|24hr Gas|24hr Water|Choke|Test Method|Allocate?|Notes|

Thanks
Amol!

Tbl-WellTest|690x316

[Steve]

Try:

([_THISROW] = MAXROW("Tbl1", "_ROWNUMBER", ([_THISROW].[ID] = [ID])))

[Douglas_English]

i tried mimicing this

[Steve]

ABBA:

I have the Table with ID, Date, O, W,G

Replace ID with whatever you were referring to with “ID” in the above.

[ABBA]

ID was “Well Name”
It still gives me multiple rows of same Well Name

([_THISROW] = MAXROW(“Well Tests”, “_ROWNUMBER”, ([_THISROW].[Well Name] = [Well Name])))

[Steve]

Let’s try this, then:

([key-column] = MAXROW(“Well Tests”, “_ROWNUMBER”, ([_THISROW].[Well Name] = [Well Name])))

replacing key-column with the name of the key column for the Tbl1 table.

[ABBA]

ABBA:

([_THISROW] = MAXROW(“Well Tests”, “_ROWNUMBER”, ([_THISROW].[Well Name] = [Well Name])))

I tested again, the last one before this _THISROW solution works better
Thanks for your very kind help.

[ABBA]

([_THISROW] = MAXROW(“Well Tests”, “_ROWNUMBER”, ([_THISROW].[Well Name] = [Well Name])))

This gives me LastROW.
Would like to get the most recent well test, based on Date.

Thanks,
Amol

[Steve]

I encourage you to read the MAXROW() documentation I posted earlier in the thread. You requirement can be met with a small change in the expression.

[ABBA · Accepted solution]

Thanks Steve.
I changed _ROWNUMBER to “Date”…Thanks to @Marc_Dillon

SOLVED

([_THISROW] = MAXROW(“Well Tests”, “Date”, ([_THISROW].[Well Name] = [Well Name])))

[Douglas_English]

I was really hoping this MAXROW expression of mine would work for a slice I am using simply to display the most recent office hour…

MAXROW(“Office Hours”, “_RowNumber”, [kot]=USEREMAIL())

[kot] refers to manager (name) key being email…

it check outs in expression but doesn’t work for the slice, i tried all sorts of variations but … this should work in my mind!

…we have one table of Office Hours, I want the most recent entry for the current user

[Steve]

Douglas_English:

MAXROW(“Office Hours”, “_RowNumber”, [kot]=USEREMAIL())

Try using the form in the topic’s solution:

ABBA:

([_THISROW] = MAXROW(“Well Tests”, “Date”, ([_THISROW].[Well Name] = [Well Name])))

[Douglas_English]

ABBA:

([_THISROW] = MAXROW(“Well Tests”, “Date”, ([_THISROW].[Well Name] = [Well Name])))

YESSSS SIRR!!!

[ABBA]

Hi Steve
How can I get data only for EOMONTH()

Thanks Buddy!
You are a trooper

Regards,
Amol

[Steve]

ABBA:

How can I get data only for EOMONTH()

I don’t understand. Please elaborate.

[ABBA]

Thanks Steve
I am going to write another question, since its not related to this Table.

Thanks
Amol

[kasun]

ABBA:

_ROWNUMBER

The answer in MAXROW is “True” or “false”. How do I get the selected coloumn value of the last row in the table there? This is my equation. I applied this to the initial value. The answer is false.

[_THISROW] = MAXROW(“stock pile”, “_ROWNUMBER”,([_THISROW].[Stockpile No] = [Stockpile No]))

Can you help me?

[Steve]

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How do I get a column value from the last row of this thing only? LOOKUP( MAX( SELECT( My Table[_ROWNUMBER], ([_THISROW].[Thing] = [Thing]) ) ), "My Table", "_ROWNUMBER", "Wanted Column" ) Replace My Table with the name of the table from which you want the column value; Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID); and Wanted Column with the name of the column whose value you want. See also: MAX()

[kasun]

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT()

LOOKUP( MAX( SELECT( My Table[_ROWNUMBER], ([_THISROW].[Thing] = [Thing]) ) ), “My Table”, “_ROWNUMBER”, “Wanted Column” )

Great. Fantastic​:+1:

[Aridaman]

Dear sir, I have a table called as Patient Monitoring. I have created an application in which a care giver visits customers and logs details , I want to display the date of the last record that he entered for this one particular customer. I have been using this forumula

LOOKUP( MAX( SELECT( Patient Monitoring[_ROWNUMBER], ([_THISROW].[Phone Number] = [Phone Number]) ) ), "Patient Monitoring", "_ROWNUMBER", "Current Visit Date") 

[ABBA]

Steve:

([_THISROW] = MAXROW(“Tbl1”, “_ROWNUMBER”, ([_THISROW].[ID] = [ID])))

I tried and get an error

EXPRESSION ASSISTANT

([_THISROW] = MAXROW(“Well Tests”, “_ROWNUMBER”, ([_THISROW].[ID] = [ID])))

Error in expression ‘[_ComputedKey].[ID]’ : Unable to find column ‘ID’