find max values
Hello,
I have data that looks like this:
a,1
a,2
a,3
b,1
b,2
c,1
c,2
c,3
c,4
d,1
e,1
f,1
f,2
I want to add a beast mode field so that the data in that field displays like this. In this way the beast mode field would return the highest value from counting the first two fields.
a,1,-
a,2,-
a,3,3
b,1,-
b,2,2
c,1,-
c,2,-
c,3,-
c,4,4
d,1,1
e,1,1
f,1,-
f,2,2
I hope that makes sense! I'm a beginner in these statements having made a few case and date statements. Appreciate any available input or direction ofn where to start!
Best Answer
-
This is not something that you can accomplish with a beast mode.
I did this with a windowed select statement using a Redshift DataFlow:
select
"type"
,"number"
,MAX("number") over (partition by "type") as "max" " -- this will add a column with the max "number" value for each "type"
from dojo_help
order BY "type" ASCI saved this as max_values and then did this transform to match your desired output:
SELECT
"type"
,"number"
,case
when "number" = "max" then cast("number" as CHAR) -- in order to have '-' in the rows that do not have a max value you need to cast "number" as a CHAR
else '-' end as "highest value"
FROM max_values
order by "type" ASC, "number" ASC0
Answers
-
This is not something that you can accomplish with a beast mode.
I did this with a windowed select statement using a Redshift DataFlow:
select
"type"
,"number"
,MAX("number") over (partition by "type") as "max" " -- this will add a column with the max "number" value for each "type"
from dojo_help
order BY "type" ASCI saved this as max_values and then did this transform to match your desired output:
SELECT
"type"
,"number"
,case
when "number" = "max" then cast("number" as CHAR) -- in order to have '-' in the rows that do not have a max value you need to cast "number" as a CHAR
else '-' end as "highest value"
FROM max_values
order by "type" ASC, "number" ASC0 -
Many thanks!
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 656 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 811 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 80 App Studio
- 45 Variables
- 771 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 77 Workflows
- 23 Code Engine
- 36 AI and Machine Learning
- 19 AI Chat
- AI Playground
- AI Projects and Models
- 17 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 142 Manage
- 138 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive