Beastmode across seperate rows, is it possible?

user046467
user046467 Member
in Magic ETL

Not sure if this is going to work at all or that I made a mistake.

 

I've added 2 screenshots.

One with the actual beastmode script and a screenshot from the table.

Is it possible to exectue this script? I get a valid response but it doesn't return any values. Most likely because it's a script across multiple rows.

 

Hopefully some can help me out or just straight up tell me that it won't work at all

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @user046467 

     

    This is because your CASE statements have no ELSE clause causing them to return NULL. When SUM comes across NULL it treats the entire value as NULL. To correct this you'd need to make sure your CASE statements return a value. In this case since you're adding values together with SUM you can add 'ELSE 0' to each of your case statements to fix your beast mode.

    **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

    While @GrantSmith 's assessment that you lack an ELSE clause is not untrue, I don't believe it'll get you to the heart of the problem.

     

    SHORT ANSWER.  Restructure your data or you'll probably get the wrong result.  

     

    LONG ANSWER

    Yes ... you could aggregate the data and get a result.

     

    SUM(CASE WHEN 'ConversionTypeName 2` = 'Sale 1' then ConversionValue end)  /// yields 98.

    *

    SUM(COST) // yeilds 72.17

    // yeilds 7072.66

     

    Which I assume is 'wrong'.

     

    What you probably want to do is get the conversion rate into the same row as Cost using a JOIN somehow.  Alternatively, you just want ONE conversion rate for type Sale 1. and then the SQL i wrote earlier will work.

    SUM(CASE WHEN 'ConversionTypeName 2` = 'Sale 1' then ConversionValue end)  /// yields 48.

    *

    SUM(COST) // yeilds 72.17

     

     

    But then you cannot have AccountDescriptiveName or ConversionType on your axis.  Because NO you cannot aggregate across rows. **

     

    ** technically you can aggregate across rows, but given the SQL you're writing and the use case you're describing, I don't believe window functions are appropriate.

     

    Hope that helps.

    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"
  • user046467
    user046467 Member

    Thanks you both for your response.

    I ended up restructuring my data and making the calculation much simpler. By using join and adding a constant to the conversions tab. That seems to work ?

Categories