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 Desktop Discussions

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

Min not zero, source of Min/Max

jmarleigh
7 - Meteor

I was wondering if someone could help me out with doing a MIN across multiple columns, but not coming back with values that are 0. 

 

The other question is that if I use Min/Max how I can determine the source of where the value is coming from?

 

Thanks,

Justin

7 REPLIES 7
Joe_Mako
12 - Quasar

Can you make up some sample data to represent your situation, what you have for input, and what you expect for output? Thank you!

Philip
12 - Quasar

Hi @jmarleigh

 

Here's one way to approach it.

 

1. Transpose data to get all the column names in one column and values in one column.

2. Retain only values > 0.

3. Sort data by the record ID, then by the value column.

4. Use Summarize to take first (minimum due to the sort) and last (maximum due to the sort).

 

Does this get the result you are looking for?

Let me know if something isn't clear or there is something I missed.

 

 

First and Last as Min and Max.png

jmarleigh
7 - Meteor

Sure, below is what I'm expecting to happen, I just want to know how to get the Min of the 3 values (Column 6), but not the one with a 0 in it, and also have another column tell me the header of where the min came from; in this case it would be value 2 (Column 7). Right now when I drop a formula into the workflow and use the min function it will give me 0 from Value 3, instead of 4 from Value 2.

 

StoreSKUValue 1Value 2Value 3MinSource of Min
852110001001035404Value 2

 

I was thinking something like a Multi Field Formula would work to change all the 0s to NULL and then just run the MIN formula right after would work, but I don't know how to change all the 0s to NULL values in Alteryx. Same goes for showing the source of the min value. I've done this in Excel, but I can't translate it to Alteryx.

Philip
12 - Quasar

The Multi-Field Formula will turn 0 to NULL using this formula:

 

IF [_CurrentField_] > 0 THEN [_CurrentField_]

ELSE NULL()

ENDIF

 

Then you can use the MIN function for the fields. But it won't give you the column.

The workflow above with give you both the MIN and the column, plus the MAX and the column for that.

jmarleigh
7 - Meteor

Philip,

 

Thanks for the formula. I downloaded your previously posted workflow, but was unable to open it because you're using a newer version of Alteryx than I am so I received an error. 

 

Is it possible for you to just walk me through what's required to find the source of the min/max after the 0s are filtered out?

 

Joe_Mako
12 - Quasar

Does the attached work for you?

jmarleigh
7 - Meteor

Works perfectly.

 

Thank you Joe.

Labels