Fetch value(not key) from Last Row with condition

AppSheet AppSheet Q&A
1

Hello everyone,

I have two table: Table A for registering new water ponds, and Table B which registers water level over time for different ponds.

I need the Deck view for Table A to show a VC with the last water level registry.
So I can see at a glance what´s the water level of that particular waterpond.

The two problems I´m facing:

  1. I cannot use [WaterLevel] as a Key as it does not have Unique values. So MAXROW() didn´t work for me.
  2. I would need to add a condition so that the water level matches each pond.

I solved problem #1 with this formula:
LOOKUP( MAX(Table B[_ROWNUMBER]), “Table B”, “_ROWNUMBER”, “WaterLevel” )

This way its showing the last “WaterLevel” for all ponds.
Im still missing the condition so that the “Ponds” match between “Table A” and “Table B”. Any ideas on how to add this?

Thanks for reading!

2

Try:

ANY(
  SELECT(
    Table B[WaterLevel],
    (
      [_ROWNUMBER]
      = MAX(
        SELECT(
          Table B[_ROWNUMBER],
          ([Pond] = [_THISROW].[Pond])
        )
      )
    )
  )
)

  1. SELECT(Table B[_ROWNUMBER], ([Pond] = [_THISROW].[Pond])) gets a list of row numbers for all rows in Table B wherein the Pond column value matches the Pond column value of this (Table A) row.

  2. MAX(...) gets the largest of the row numbers from (1).

  3. SELECT(Table B[WaterLevel], ([_ROWNUMBER] = MAX(...))) returns a list of the one WaterLevel column value from the one row in Table B having the _ROWNUMBER column value matching the one found by (2).

  4. ANY(...) converts the list of one value given by (3) into a singular value.

5 Likes
3

Worked perfectly!
Once more you nailed it!
I´ve been going around this for days.

Thanks @Steve !

2 Likes