Min not zero, source of Min/Max

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

Transformation

Accepted answers

Philip

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.

Joe_Mako

Does the attached work for you?

min value.yxmd

All comments

Joe_Mako

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

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

First and Last to get Min and Max.yxmd

jmarleigh

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.

Store	SKU	Value 1	Value 2	Value 3	Min	Source of Min
8521	1000100103	5	4	0	4	Value 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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703