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

Pierce13

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!

MarkSnodgrass

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.

MarkSnodgrass

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.

Pierce13

Thank you. I will give those suggestions a shot!

Pierce13

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