How do I return a string column value from the MAX() of another column value?

Pierce13
Pierce13 Member
in Magic ETL

Let's say I have the following:

image.png

I want to group by the invoice # by dropping the SKU, returning the Product_Description of the MAX Product_Weight, and the SUM of the Product_Weight. With my given example I should return the following:

image.png

I have tried in both Beast Mode and an ETL, but my Product_Description is never accurate. In ETL I would perform a group by on the Invoice # and do the following formula for Product_Description:

image.png

It returns an inaccurate product description every time.

Thanks!

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    If you have window functions in beast modes enabled in your instance, you could this in analyzer. It would look something like this

    case when MAX(`product_weight`) OVER(PARTITION BY `invoicenumber`) = `product_weight` then `product_description` end

    In your card, drag the invoice number and this beast mode into your card. Also, drag this beast mode into your filters and choose Not In blank.

    I did some light testing and this should work. If you don't have window functions in beast modes, you can ask your CSM to enable it.

    In Magic ETL, you could use Rank & Window to rank by the product weight descending and then filter to rank equals and then join it back to you main dataset. Something like this:

    image.png

    Hope this helps.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    If you have window functions in beast modes enabled in your instance, you could this in analyzer. It would look something like this

    case when MAX(`product_weight`) OVER(PARTITION BY `invoicenumber`) = `product_weight` then `product_description` end

    In your card, drag the invoice number and this beast mode into your card. Also, drag this beast mode into your filters and choose Not In blank.

    I did some light testing and this should work. If you don't have window functions in beast modes, you can ask your CSM to enable it.

    In Magic ETL, you could use Rank & Window to rank by the product weight descending and then filter to rank equals and then join it back to you main dataset. Something like this:

    image.png

    Hope this helps.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Pierce13
    Pierce13 Member

    Thank you. I will give those suggestions a shot!

  • Pierce13
    Pierce13 Member

    I need to do more testing, but on first glance the beast mode seemed to work. Thank you!

Categories