Alteryx designer Discussions

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

identifying the max value across columns

Highlighted
Alteryx Partner

Hi there

 

I am analyzing survey data and the respondents were asked to allocate 100 points across 7 attributes. The way the data is displayed, is that each attribute is a separate row, with the respondents' scores for each attribute in separate columns:

IDQB2R1QB2R2QB2R3QB2R4QB2R5QB2R6QB2R7QB2R97QB2RSCTNEW_COLUMN
010202001040100100QB2R6
025510203020100100QB2R5
0310010201010400100QB2R7

 

I want to define a new column that returns the column name of the column with the highest score across all of the columns (see example "NEW_COLUMN" above). Is there a way to do this?

 

If a participant has entered the same score for the multiple top columns, then I just want the formula to return one column name (doesn't matter which one).

 

Please help!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

I have attached one possible solution to your problem. I transposed your data, sorted, and took the first record name to append to the original data.

Highlighted
Alteryx Partner

The attached workflow seems to do the trick

 

MaxColumnValue.PNG

Highlighted
ACE Emeritus
ACE Emeritus

Rather than transposing your entire dataset, I took a bit of a different approach with a really neat formula function. 

Highlighted
Alteryx Partner

Thank you Charlie, I ended up using the Sample Tool in lieu of using a Max function for the following scenario.

 

Column A was my Key Field with duplicate results (e.g., State). 

Column B was a unique value (e.g., City) but in every State field there were Cities with Null values.

Column C was the change in population sometimes negative sometimes positive.

 

I grouped by Column State and City, so there was only one null value per state.  It may be negative of positive.

 

I wanted to populate the Null value with the max/min for each state.  If AZ Null was negative, I wanted to pull AZ City that had the largest positive increase and say the two offset each other to lower the swing.  If AR was positive, I wanted to pull the AR City that had the largest negative increase (or smallest positive) and say the two offset.

 

Using Sample Tool with Filters to do a left / right side approach worked.  I was going to try split the reduction from the min max and say could not be greater than for the total nulls for the state and pull the difference from the next value, but for another day.

 

Attached is my sample data.  I did not have a clean workflow to share but can if anyone ever needs.  I will update one day, if I learn how to do the same with formulas.

 

This was useful with SAP journal entries where a trader code was not populated for each line item, only the first time until the trader changed and I had to prepopulate the missing trader.

Labels