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
-
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.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Answers
-
Thank you. I will give those suggestions a shot!
1 -
I need to do more testing, but on first glance the beast mode seemed to work. Thank you!
1
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有