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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Answers
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 303 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 104 SQL DataFlows
- 640 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 769 Beast Mode
- 72 App Studio
- 43 Variables
- 718 Automate
- 185 Apps
- 462 APIs & Domo Developer
- 57 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 402 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 9 Software Integrations
- 135 Manage
- 132 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive