Creating a Pass/Fail Test in Magic ETL

gbrown
gbrown Member
edited March 2023 in Magic ETL

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

image.png

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

Tagged:

Answers

  • RobSomers
    RobSomers Coach

    @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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories