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
Solved! Go to Solution.
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
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
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
Thank you for your timely Help.
Thanks
Anthony Raj