find max values

colinr
colinr Member
in Magic ETL

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

  • ST_-Superman-_
    ST_-Superman-_ Coach
    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:

    1.png

     

    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" ASC

     

    I 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" ASC

     

    2.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    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:

    1.png

     

    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" ASC

     

    I 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" ASC

     

    2.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • colinr
    colinr Member

    Many thanks!

Categories