cds view max for each category

[truckla]

Hi,

I want to show the max of the category.

So that I get a Column that shows if it is the maximum price of this category.

What should I use and do?

[pfefferf · Accepted solution]

You are using ABAP CDS Views?

If yes, you can use a CDS Table function (as suggested in the answer before). Instead of the RANK function you can use the Window Aggregate Function MAX too to determine the max value to be able to compare it against the price.

Another simple approach without using a CDS Table function is to define an additional CDS view which provides the maximum value for the category (with a simple max on the price grouped by the category). The in your view which should provide your result you can join that additional view. With a case you can compare the Price with the joined maxium Price and set the value of your "Max ?" column.

[truckla · Accepted solution]

Hi florian.pfeffer

I am using ABAP CDS Views.

I tried it with two views, but unfortunately I can't get it right.

The problem is, if two categories happen to have the same price, the wrong result will come out. For example,

if category A has the max value 5
Category B has the max value 7
And it then has a product in category B with the value 5, it is marked as max because the 5 of category A in the view with the maximum values.

Can you please help me?

Maybe my code can help to explain:

define view ztt_products
  as select from database    as db
    cross join   database2 as db2
association [0..1] to ztt_max as t_max on t_max.key = db.product_id
{
  key db.product_id    as key,

      @UI.lineItem.position: 20
      @UI.lineItem.label: 'Price'
      db.price   as price ,

      @UI.lineItem.position: 30
      @UI.lineItem.label: 'max?'
         case 
           when db.price = t_max.maxvalue  then  'yes'
      else 'no'
      end                  as isMax,
    
    

 
      @UI.lineItem.position: 40
      @UI.lineItem.label: 'Category'
      db2.categroy  as productCat
      
}
group by db2.category,db.product_id,db.productname, db.price,  t_max.maxvalue

define view ztt_max as select  from database    as db 

{

         
     key db.product_id    as key,

      

max(db.price) as maxvalue
} 
group by db.product_id

[pfefferf · Accepted solution]

You said that you need the maximum price per category, so it is not clear to my why you group by product_id instead of the category in view ztt_max (with category as key) and do the association via the category.

[truckla · Accepted solution]

Hi,

due to if I don't put the product_id in the group by, an error comes: The column "product_id" is not in the GROUP BY list includet". And I need the id for the association.

I forgot the category

Now my second view looks like:

define view ztt_max as select  from database    as db 

{

         
     key db.product_id    as key,

      

max(db.price) as maxvalue
} 
group by db.category, db.product_id

If you still see something wrong, what should I improve or change?

[pfefferf · Accepted solution]

And for what you need the product id in the production? You are insterested in the max price per category.

So why not like following???

define view ztt_max as select  from database    as db 
{
     key db.category
         max(db.price) as maxvalue
} 
group by db.category

[truckla · Accepted solution]

Hi,

thank you very much, it works now. Not perfectly because I have somewhere another problem.

I have many duplicates of each entry. Can you see what it might be in my views?

[pfefferf · Accepted solution]

I guess that is because of the cross join and the underlying data. But you have to answer that yourself, because you are the person who knows the data and what you want to reach with the cross join.

[truckla · Accepted solution]

I was happy too early
Now a single value is taken as maximum for all, the largest value at all

I use the cross join because I do not know how else to use two databases in one view
Need from database2 the category