Solved! Go to 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:
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
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
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.
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
Hi @Benjamin Sasin? You can do this with a combination of MAX() and UNIXTIMEFORMAT() functions in 2 steps:
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