Creating a Pass/Fail Test in Magic ETL

gbrown

I am trying to create a pass/fail test in magic ETL.

There are four test it needs to go through to pass or fail. Once a row is listed as fail I would want all the rows matching that submission ID to fail as well how would I go about this or what is the correct formula to use?

for example It failed the first test but passed the next three. Since the first test failed I would want each row under it to say fail as well…

Here is the formula to determine a pass/fail for reference:

CASE WHEN Critical/Major= 'Major' AND No>= 2 THEN 'Fail'
WHEN Critical/Major= 'Critical' AND No> 0 THEN 'Fail'
WHEN Critical/Major= 'Major' AND No= 1 AND Critical/Major= 'Common' AND No>= 2 THEN 'Fail'
WHEN Critical/Major= 'Common' AND No>= 3 THEN 'Fail'

ELSE 'Pass' END

Thanks

RobSomers

@gbrown I would suggest doing this by splitting your dataset once you have the Pass/Fail beastmode created as written. Have a branch that filters to just the fail rows and then you can select just the Submission ID and Pass/Fail columns and then use the Remove Duplicates tile. Then just join it back to the “original” branch of your dataset using the submission ID and you'll have all ID's with a single fail marked as fail.

GrantSmith

Alternatively you can use a group by tile and select the minimum value for your pass fail column and join it back to your original dataset that way if any are marked as fail all would become fail.