Hi I have been using excel index match to return the field name of a matching value. Wanting to know how this can be done in Alteryx? Thank you for your assistance Input ITEMHeight CheckWeight CheckDiameter CheckPassed All ChecksReason for Failing ( Pick one only)A101FAILHeight CheckB100FAILHeight CheckC111PASS D011FAILWeight CheckE111PASS Output ITEMOUTPUTAFAIL - Height CheckBFAIL - Height CheckC DFAIL - Weight CheckE

Return the Field Name

I have been using excel index match to return the field name of a matching value. Wanting to know how this can be done in Alteryx?

Thank you for your assistance

Input

ITEM	Height Check	Weight Check	Diameter Check	Passed All Checks	Reason for Failing ( Pick one only)
A	1	0	1	FAIL	Height Check
B	1	0	0	FAIL	Height Check
C	1	1	1	PASS
D	0	1	1	FAIL	Weight Check
E	1	1	1	PASS

Output

ITEM	OUTPUT
A	FAIL - Height Check
B	FAIL - Height Check
C
D	FAIL - Weight Check
E

Tips and Tricks

Accepted answers

YLYONG

@apathetichell @SoccerTil @atcodedog05 @mceleavey

Here is the solution:

This simple workflow can potentially be simplified by Cross Tab.

Happy if anyone wanted to give it a try.

Cheers

ylyong_solution.yxmd

All comments

mceleavey

Hi @YLYONG ,

This is a simple formula:

if [Passed All Checks]="FAIL" then "FAIL - "+[Reason for Failing ( Pick one only)] else "" endif

Workflow attached. I hope this helps.

mceleavey_solution.yxmd

atcodedog05

Hi @YLYONG

Here is how you can do it. Is this what you are looking for?

Workflow:

Hope this helps : )

Height Check - FAIL.yxmd

SoccerTil

Or are you looking for a simple lookup and replace. See the widget Find Replace as in this example.

simple Find Replace.yxmd

apathetichell

Reading your description I think you want to dynamically pull in to the column heading where you have a 0 but your data doesn't support that hypothesis. The easy way to do that would be to do something like record id and transpose and then declare the reason for failing is whatever is a zero and then cross tab back. There's more nuance to this (like testing if something doesn't fail and writing pass) but that's basically the strategy.

Did you mention what happens if there are multiple 0s? Does a zero designate failure and a specific attribute causing that failure?

YLYONG

Thank you for your valuable input @SoccerTil @apathetichell @atcodedog05 @mceleavey

I guess what I want the workflow to do is when an item fails, then dynamically pick the first field as the reason for its failure.

For instance, the reason for the failure of item B was Weight Check

Input

M	Height Check	Weight Check	Diameter Check	Passed All Checks
A	1	0	1	FAIL
B	1	0	0	FAIL
C	1	1	1	PASS
D	0	1	1	FAIL
E	1	1	1	PASS

Output

TEM	OUTPUT
A	FAIL - Height Check
B	FAIL - Height Check
C
D	FAIL - Weight Check
E

YLYONG

@apathetichell @SoccerTil @atcodedog05 @mceleavey

Here is the solution:

This simple workflow can potentially be simplified by Cross Tab.

Happy if anyone wanted to give it a try.

Cheers

ylyong_solution.yxmd

atcodedog05

Hi @YLYONG

If have limited columns you can use the formula tool else if directly.

Workflow:

Hope this helps : )

ylyong_solution.yxmd

Akash__on

Let's solve it step by step:

We need to find the FAIL candidates and their reasons with the OUTPUT field having FAIL and the reason specified in the input data.
Simply use Formula tool : Apply the conditional statement checking the "passed all checks" field = "FAIL"
If condition is true "FAIL-" + [reasons for failing...]
Else leave it blank.

I hope that will help.

Akash__on

okay, you finally got it.. i only saw the first post.

Thanks for posting.

YLYONG

@Akash__on @atcodedog05 @apathetichell @SoccerTil @mceleavey thank you so much for your ideas and suggestions!

atcodedog05

Happy to help : ) @YLYONG

If our response helps please don't forget to mark it as solution.

Cheers and have a nice day!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15872

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703