Last entry in row beginning with

[Stephen_Monagha]

Hello,

is it possible to find the last entry in a table where a column has a certain prefix?

e.g.
if I have rows with the following values for a certain column 1.1, 1.2, 2.1, 3.1, 1.3, 3.2., I would like to be able to find the last row beginning with a 1, or a 2 or a 3.

i.e. if I look for 1, the answer would be 1.3, if I look for 2, the answer would be 2.1, and if I look for 3 the answer would be 3.2.

Hopefully I’ve explained it ok.

Thanks for any help.
Steve

[Suvrutt_Gurjar]

Hi @Stephen_Monaghan,

I believe some more details could help the community members to assist in a better manner.

Could you please mention where and how you would like to capture that indicator of last entry? In another column?

Also if it is in another column would you like three statuses to be captured on that indicator columm? For example row where 1.3 value is there,the indicator says “Last entry for 1 Series” the rowwhere 2.1 is there, the indicator says “Last Entry for 2 Series” and so on.

Is last entry in terms of maximum value for that series , meaning are the values in a series always increasing when rows are added or are they random in entry orderand still last entry means maximum value.

[Stephen_Monagha]

Hello @Suvrutt_Gurjar,

thanks for you reply.

I would like to put this expression in a text column in a separate table. I will only ever use one at a time, but I will put them in a switch statement which will be dependent on a drop down menu option. Rows for a series are always in increasing value. e.g. 1.7 will always be in a row after 1.6 (but not always consecutive)

I have managed to write the following expression which seems to return the list I am looking for (I am still working on it so I’m not 100% sure) i.e. it filters out all the columns that don’t have prefix “1.”. I am still working out how to get the last entry in the list though.

SELECT(Table Name[Column], LEFT([Column],2)=“1.”)

Thanks,
Steve

[Suvrutt_Gurjar]

Hi @Stephen_Monaghan,

Thank you for the updates. I believe you are proceeding correctly. If you are looking for maximum value of that column then , I believe you may wrap your SELECT statment with a MAX() function.

help.appsheet.com

MAX()

Highest item in list

If you are looking for the key value of the row with the maximum value in that series column, then I believe you may wish to take a look at MAXROW() function.

help.appsheet.com

MAXROW()

Row with greatest value in column

[Stephen_Monagha]

Hi @Suvrutt_Gurjar,

thanks for the tips.

I wasn’t able to use MAX as if I treat the values as numbers then 1.1 and 1.10 will be the same value. So I had to use text and use the following expression

INDEX(
SELECT(TableName[Column], LEFT([Column],2)=“1.”),
COUNT(SELECT(TableName[Column], LEFT([Column],2)=“1.”))
)

Is there a better way of getting the last entry in a list, rather than running the same expression twice i.e. once to get values, and 2nd time to count values.

Thanks,
Steve

[Steve · Accepted solution]

What you’re trying to do is hugely expensive and inefficient. You should consider a different numbering system. At the very least, make the number column type Decimal rather than Text, then it’s as simple as:

MAX(
  SELECT(
    MyTable[CertainColumn],
    AND(
      ([CertainColumn] >= 2.0)
      ([CertainColumn] < 3.0)
    )
  )
)

[Steve]

Another option, along the same line as my last suggestion, but to get the row itself with the highest value:

ANY(
  ORDERBY(
    FILTER(
      "MyTable",
      AND(
        (DECIMAL([CertainColumn]) >= 2.0)
        (DECIMAL([CertainColumn]) < 3.0)
      )
    ),
    DECIMAL([CertainColumn]),
      TRUE
  )
)

This assumes CertainColumn is of type Text. FILTER() gathers rows within the desired range. ORDERBY() orders the resulting rows by their CertainColumn values, highest-to-lowest. ANY() returns the first of the sorted rows–the row with the highest CertainColumn value within the desired range. If CertainColumn is of type Decimal (as I suggested previously), remove the DECIMAL() references in the above.

[Stephen_Monagha]

Hi @Steve,

thanks for you’re suggestion. I’ve changed my numbering system based on your advice. I’m using the ranges 1000, 2000, 3000 etc now, as I don’t expect these ranges to be fully used.

Thanks.
Steve