Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Cloud Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Cloud.
SOLVED

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.

TrifactaUsers
10 - Fireball
 
5 REPLIES 5
Trifacta_Alumni
Alteryx Alumni (Retired)

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
10 - Fireball

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
8 - Asteroid

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
Alteryx Alumni (Retired)

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
Alteryx Alumni (Retired)

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