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

Let's say I have the following:

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:

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:

It returns an inaccurate product description every time.

Thanks!

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    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:

    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

  • Thank you. I will give those suggestions a shot!

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