Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find busiest CarrierId based on FlightDate using SAP CDS View

AlokKumar
Discoverer

2 weeks ago

0 Kudos
Hi Experts ,
I need to get the below expected output. Let me explain: as 07.04 CarrierId has highest number of trip that is 4 so it should be the output instead of 10.02. All other entries have single entry based on date so it will be in output.

Input data:

CarrierID Connection ID FlightDate Price
UA175010.02.2024250
UA189007.04.2024450
UA276007.04.2024150
UA197007.04.2024100
AA349011.04.2024340
AA196013.04.2024560
SQ349015.05.2014120

 

Expected output:

CarrierID FlightDate
UA07.04.2024
AA11.04.2024
AA13.04.2024
SQ15.05.2014

I tried to create two CDS Views to solve the issue but I can only get the busiest CarrierId without date in output. But requirement is to display the date with CarrierID.

I tried to find output with the below views:

  1. define view entity View1 as select from /dmo/booking  
{
 key carrier_id as CarrierId,
     flight_date as FlightDate,     
  count(*) as FlightCount   
}
group by
 carrier_id,flight_date
  2. define view entity View2 as select from View1
{
 key CarrierId as CarrierId,
     FlightDate as FlightDate,
 max(FlightCount) as Numberofflight
}group by CarrierId

    This view is not working as we need to add non-aggregated fields in the Group By clause but if I add FlightDate in the Group By then the output will be different.
1 REPLY 1