Case when.. multiple parameters

user097047
user097047 Member
in Magic ETL

I would like to make a beastmode for a column that contains integers, as well as null values instead of zero. However, my current formula returns null as zero, but the metric that i use is for purchase price variance, and one of my accounts has duplicate values for invoice price and ppv. For example, it would show $65.00 for invoice value and for PPV, because the invoiced items for that account has no standard cost. So essentially, i would like my beastmode to say:

if "ppv" is null, then 0, else return "ppv", and if "ppv" = invoice value, then 0.

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @user097047 

     

    If I'm understanding your request correctly you're saying:
    -IF PPV is null use 0 in the comparison to the invoice value, otherwise use the PPV value.

        - If the resulting number PPV value is the same as invoice value then return 0 otherwise return the PPV value?

     

    With that assumption this should likely work for you:

    CASE WHEN IFNULL(`ppv`, 0) = `invoice value` THEN 0 ELSE IFNULL(`ppv`, 0) END

     

    If my assumptions are incorrect please let me know.

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

    instead of writing a beast mode with obscure logic, why not clean up the data in ETL?  you don't want your data to be non-intuitive and require a data dictionary in order to use it because that means every future user has to 'know the rules' in order to build or alter a card.

     

    change your word order

    if "ppv" is null, then 0, else return "ppv", and if "ppv" = invoice value, then 0.

     

    CASE

    WHEN "ppv" is null THEN 0

    WHEN "ppv" = "invoice value" THEN 0

    ELSE "ppv"

    END

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories