Understanding Max() Function within context of Filter Tool

[Animanga008]

Hi all, wanted to confirm an understanding regarding the Max() function and Formula tool as a means of dynamically identifying a largest value in a column and then using that result against another column.

After Googlin' and looking at the documentation (https://help.alteryx.com/current/designer/minmax-functions), is it correct to say that you can't combine the two to do a dynamic Max value lookup on your dataset columns, especially if column values are non-integers (think: calendar dates)?

For example, you can't write something in the Formula tool like: [column value A] > max([column value B]), correct?

It looks like at best, if you want to identify a max value using ONLY the max() and Formula tool:

1. Your values would have to be integers.
2. You would have to creatively write a Bound() statement within a IF-statement and hard code in upper/lower bound values so that ideally your data would never exceed. For example: IF Bound(column[A],-999999,999999) THEN "option A" ELSE "option B" ENDIF.

For now, it seems like if you want to dynamically identify the max value of a column, you have to utilize Sort() or Summarize() in some form or another?

[Jonathan-Sherman · Accepted solution]

Hi @Animanga008,

The formula tool works at a row level, therefore will not calculate the max value in a column. You could use it to find the max value for each line across multiple columns however.

If you're wanting to find MAX() of a column you could use a summarise and append fields before your filter tool.

Regards,

Jonathan

[Animanga008]

@Jonathan-Sherman, ah right...it scans row-by-row. So even if you use BOUND() on a integer column, it's not going to identify the max value of a column. You would have to identify previously your max value using some other function or tool, and then hard-code that value into your BOUND() function. But at that point, the amount of steps is no different than the Summarize/Sort methods.

Interesting.