CDS - Copy one Line item data to another line item

[former_member632762]

Hello Experts,

Following is the data available in a table, only Line Item 1 is having data in Cleared On , Cleared Doc fields.

The remaining line items have blanks values in Cleared On , Cleared Doc fields.

I need to create a CDS VIew such that it should use / copy the same values of Cleared On , Cleared Doc fields of Line item 1 to Line item 2 , Line item 3 , Line item 4.

i.e.

For Line item 2, Line item 3, Line item 4

All the Blank fields in Cleared on column should be filled / copied from Line Item 1 with 01-Sep-23,

All the Blank fields in Cleared Doc column should be filled / copied from Line Item 1 with 2001,

Expected outcome of CDS view should be as shown.

Thanks,

Vijay

[guna91]

Hi,

You can try this as well and see if it helps.

First CDS view:

@AbapCatalog.sqlViewName: 'ZIntermediateCDS'
@EndUserText.label: 'Intermediate CDS for Line Item 1'
define view ZIntermediateCDS as select from YourTableName {
  Emp_code,
  EMP_Doc,
  Cleared_on,
  Cleared_Doc
} where Line_item = 1;

Second CDS view:

@AbapCatalog.sqlViewName: 'ZCopyClearedData'
@EndUserText.label: 'Copy Cleared Data from Line Item 1 to others'
define view ZCopyClearedData as select from YourTableName as main
  left outer join ZIntermediateCDS as inter
  on main.Emp_code = inter.Emp_code and main.EMP_Doc = inter.EMP_Doc
{
  main.Emp_code,
  main.EMP_Doc,
  main.Line_item,
  main.Key,
  coalesce(main.Cleared_on, inter.Cleared_on) as Cleared_on,
  coalesce(main.Cleared_Doc, inter.Cleared_Doc) as Cleared_Doc
}

[filipn]

Hello,

You will need two CDS views:

(sorry for the mistakes, this is written off the top of my head)

Regards,

Filip

define view entity z_test as select from emp_table

{

  Emp_code, 

  EMP Doc,

  max(Cleared on) as ClearedOnNew, 

  max(Cleared Doc) as ClearedDocNew, 

} 

group by Emp_code, EMP Doc

define view entity z_test_final as select from emp_table as s1

inner join z_test as temporary on s1.Emp_code = temporary.Emp_code 

                                and  s1.EMP Doc = temporary.EMP Doc 

{

  s1.Emp_code, s1.EMP Doc, s1.Line Item, s1.Key, 

  temporary. ClearedOnNew,

  temporary. ClearedDocNew

}