Re: Help with MAX() expression

inaki_albisu · 11-08-2021 06:31 AM

Hello guys, does somebody help me with this MAX() expression? I have an ID column which has values with the word “GTW” plus an incremental number (GTW001,GTW153,GTW546) and I want to return the maximum value of that column without the expression “GTW” of course. I’m using SPLIT() and INDEX() to get the numeric part of the expression but I’m receiving an error by using MAX().

MAX(INDEX(SPLIT(GTW[ID GTW],“W”),2))

“MAX function is used incorrectly”

1minManager

Its because MAX() can only work with lists whereas INDEX() will always give a single answer.

This should work by wrapping it in a select function:

MAX(Select(
	INDEX(SPLIT(GTW[ID GTW],“W”),2),
	TRUE
	)
)

Simon@1minManager.com

Aurelien

Hi @inaki_albisu

This won’t work, as MAX will want a list of numeric values.
You will need to create an intermediate virtual column.
INDEX(SPLIT([ID GTW],“W”),2)

Then, the one you want:
MAX([ThisIntermediateColumn])

Edit:
I just noticed @1minManager answered at the same time.
@inaki_albisu you have the choice !

Edit2: @1minManager 's solution seems more relevant as it will require less calculation for your need !

Steve

My suggestion:

MAX(
  LIST(0)
  + SPLIT(
    SUBSTITUTE(
      CONCATENATE(GTW[ID GTW]),
      "GTW",
      ""
    ),
    " , "
  )
)

GTW[ID GTW] gets the list of ID GTW column values.
CONCATENATE(...) produces single a Text value from (1) (...).
SUBSTITUTE(..., "GTW", "") removes all occurrences of GTW from (2) (...).
SPLIT(..., " , ") produces a list from parts of the result of (3) (...).
LIST(0) creates a list of type Number with one value, 0.
LIST(0) + SPLIT(...) adds the results of (4) to the list of (5). Because (5) is a list of Number, the values added from (4) will be converted to Number.
MAX(...) gets the maximum value from (6) (...).