Bypass Failure Condition by exception in Alteryx Designer

Hi Friends

I am looking for solution for the below scenario. This is just an example and actual logic includes 100's condition and various logic.

I have a Employee Table, and Appeal table with me.

Employee

Emp_ID	Experience	Age	Salary	Rating
381687	3	26	16520	2
277876	4	37	13268	2
220787	10	47	14653	2
593529	1	26	15994	3
815231	2	43	12453	2
1135322	4	49	16228	1
283733	6	32	12634	3
831438	4	37	12256	3

Each employee go thru some conditions in the workflow. I will be using couple of Filter conditions to qualify. If any employee does not meet (False) condition, we need to append a Error code for rejection (Using formula tool). In the Screenshot below, Cd='E111' is a Rejection Error code for Experience Criteria, Cd='A111' is a Rejection Error code for Age Criteria and Cd='R111' is a Rejection Error code for Age Criteria.

Interestingly, few employees have been given an exception from these errors. If any employee who failed in the Filter condition , has an Entry (An error Code from which he is exempted) Appeal Information, he/she can be considered as eligible and should go thru the second criteria. I.e: Employee X failed in the Experience criteria, but has an exception Appeal in Appeal table, so he will be considered as success and go to the next condition which is Age <= 40. In the next condition if he/she fails, they should be checked against Appeal Table for exception. If any exception is given for them, they will go to next condition or they will be considered as ineligible.

Emp_ID	Code
812153	E111
1035228	E111
1038904	E111
1140425	E111
1088434	E111
494987	E111
1030577	E111
494987	A111
812153	A111
343023	A111
956730	R111
1028869	R111

Please suggest me which is the best approach to handle this.

Thanks,

Antony Raj

Workflow

Developer

Error Message

Tips and Tricks

Custom Tools

Parse

Macros

Best Practices

Transformation

Batch Macro

Join

Optimization

Interface Tools

Data Investigation

Events

Chained App

Iterative Macro

Accepted answers

danilang

hi @Antony89anto

Any formula written as a series of Nested IF-THEN statements can be rewritten using IIF syntax like this

IIF(age <=5, IIF([salary] <=15000, "True","False"), IIF([age]<=10, IIf([salary] <=65000,"True","False"),"True")).

Add this as a new formula in the Formulas table and the results will be calculated

Dan

All comments

danilang

Hi @Antony89anto

Here's how I'd handle this one

This solution is based around the Crews Dynamic Formula macro. This macro applies a series of formulas in series to a set of data. The results of each of these formulas appears as a new column of data. The Formula text input contains these looking like this

Once the macro runs, the results are like this, with one new column for each formula with "True" if the row passed this formula and false otherwise.

To handle the exceptions, we transpose the condition columns so the we can join the false ones to the Exceptions table. The ones that match get changed to "True" and everything passes through the union before being Crosstabbed back into the original format, resulting in

Note that in order to get a record to go through the exception process I had to change the first ID in the exception table to 381687. Without this change, the exception and data tables had no IDs in common.

Dan

FailureConditions.yxmd

Antony89anto

That's fantastic. Really cool information.

Could you please tell me how will you handle this, if these conditions are nested?

I.e:

If age <=5 then

if salary <=15000 then 'Ok'

else 'Not Ok' endif

elseif age<=10

if salary <=65000 then 'Ok'

else 'Not Ok' endif

Endif

I got this question out of Curiosity! Is there anyway We can do in the same way I implemented? A single table/file should be referenced by all failure data and become true and go for another check after appeal/exemption present.

Thanks

Anthony Raj

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703