Case statement not working

jp2631
jp2631 Member
in Magic ETL

Please help with my ETL formular.

I'm not sure why my case only works for…

WHEN AdditionalRecruiter = '' and RecruiterInOrder = 1 THEN 'Unassigned Owner'

WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter

WHEN AllRecruiter IS NULL THEN 'N/C'

Below is the code I'm using to populate the 'RecruiterOutcome' column. I'm using N/A, N/B, N/C, and N/D to differentiate between cases. The goal is to fill the 'RecruiterOutcome' column with any flag in the blank rows, so that I can filter them out later.

CASE WHEN AdditionalRecruiter = '' and RecruiterInOrder = 1 THEN 'Unassigned Owner'
WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter
WHEN STR_CONTAINS(AdditionalRecruiter,',') and AllRecruiter IS NULL THEN 'N/A'
WHEN STR_CONTAINS(AdditionalRecruiter,',') and AllRecruiter = '' THEN 'N/B'
WHEN AllRecruiter IS NULL THEN 'N/C'
WHEN AllRecruiter = '' THEN 'N/D'
ELSE AllRecruiter
END

image.png

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Case statements are evaluated from the top down and will stop when they find a condition that's true.

    Because you have

    WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter

    At the top of your case statement the other two below it won't ever get evaluated. Put the N/A and N/B conditions above this to have them evaluated first.

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

    @GrantSmith Thank you for your quick response. Your suggestion works well! I also realized that I made things too complicated. My formula should be as simple as the below code, then add a filter after to filter out the NULL rows in the 'RecruiterOutcome' column.

    Problem solved!!! 😀

    CASE WHEN AdditionalRecruiter = '' and RecruiterInOrder = 1 THEN 'Unassigned Owner'
    WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter
    ELSE AllRecruiter
    END

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Case statements are evaluated from the top down and will stop when they find a condition that's true.

    Because you have

    WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter

    At the top of your case statement the other two below it won't ever get evaluated. Put the N/A and N/B conditions above this to have them evaluated first.

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

    @GrantSmith Thank you for your quick response. Your suggestion works well! I also realized that I made things too complicated. My formula should be as simple as the below code, then add a filter after to filter out the NULL rows in the 'RecruiterOutcome' column.

    Problem solved!!! 😀

    CASE WHEN AdditionalRecruiter = '' and RecruiterInOrder = 1 THEN 'Unassigned Owner'
    WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter
    ELSE AllRecruiter
    END

Categories