Is there a way to get the max value across a number of fields in a row, rather than within a column? Apologies if I am missing something obvious.

[Trifacta_Alumni · Accepted solution]

Hi, Stephen--

You can do the following. Note that the LISTMAX feature is not yet available in GDP, but you can use it in Trifacta Wrangler, our free product:

Steps:

1. Create a new column. Formula: MERGE([col1,col2,col3,col4],","). Column name:'arr1'
2. Create a new column. Formula: MERGE("[",arr1,"]"). Note that there is no value separator in this one. Column name:'arr2'
3. Column arr2 should be recognized as an Array data type. If not, convert it to Array.
4. Create a new column: Formula: LISTMAX(arr2)
5. Delete unused columns that you created.

The LISTMAX function should be available in GDP soon. I do not have a date for its release.

Doc links:

https://docs.trifacta.com/display/SS/MERGE+Function

https://docs.trifacta.com/display/SS/LISTMAX+Function

Note that there are a number of other LIST* functions that may be of use if you are trying to compute across rows. Check them out here:

https://docs.trifacta.com/display/SS/Nested+Functions

Hopefully, that helps a bit. There may be another solution where you pivot column values and then compute max value within a defined group, but that is more complicated and is data-dependent.

Cheers,

-SteveO

[TrifactaUsers]

Thanks Steve, that's a clever way around the problem. I think I will use Greatest() in BigQuery for now, but will watch out for the arrival of the LISTMAX function in Cloud Dataprep

[4d78bb8719d997eb9a26]

I'm facing the same problem here and don't understand how to use LISTMAX(). As I'm typing LISTMAX([column1, column2, column3]) it returns null everywhere. (I'm trying to get the latest of multiple timestamps).

Seeing the steps above I see there's a need to MERGE([columns1, column2, column3], ",") to array1 and then MERGE("[", array1, "]") as array2, though it is not accepting this syntax.

Why is not possible to pass the array directly into LISTMAX and get the expected results? Please help thanks.

Edit: I figured out the LISTMAX doesn't take dates, so I had to convert all dates to Unix time format, then get the max, then convert the output back to desired time format.

Suggestion: I wish LISTMAX would be able to take dates by default.

[Trifacta_Alumni]

Hi, Benjamin--

LISTMAX does not accept timestamps (Datetime datatype) as inputs; https://docs.trifacta.com/display/SS/LISTMAX+Function

You might try converting your values to Unixtime and then computing some Integer versions of that. Haven't tried it, though.

Cheers,

-SteveO

[Trifacta_Alumni]

Hi @Benjamin Sasin? You can do this with a combination of MAX() and UNIXTIMEFORMAT() functions in 2 steps:

1. Choose Functions from toolbar and select Date and time function UNIXTIMEFORMAT()
2. Pass the first argument to UNIXTIMEFORMAT function as MAX(date_column) and second argument as the desired date format like "dd/MM/yyyy"

Also, as per the definition of LISTMAX) function which is also shown as a preview for all functions in Formula field, it accepts only an array column. So if you have a column of array values in each cell value, LISTMAX() will extract the maximum element for that cell value or row.

But i am curious to know what is the need to convert a column to array and then find max value using LISTMAX

Best

Vardan