CDS ABAP - Join/Union of two CDS views but with separate amount field required

[former_member607480]

Hi, I am trying to join two separate CDS views - Current Year data and Prior Year data.

These views have the exact same columns (as below) but the data in the first 5 columns can differ - making some entries unique (e.g. the lines fully highlighted below).

The effect that I need to achieve is shown in the screenshot below:

*If the first 5 columns match in both Current & Prior, the joined table needs to show one line with an amount in both the Current & Prior columns.

*If there is an entry in the Current that does not match entirely with a line in the Prior, this needs to show in the joined table with the Prior amount as Zero. (And vice versa)

What I am essentially looking for is a full outer join. I have tried all manner of Joins and Unions but none have given me the expected result. When I tried to enter a full outer join I get an error as it doesn't recognise the word 'full' (see below)

Has anyone done anything similar before? This is driving me mad so any input would be greatly received!

Thanks in advance.

Pete

[jrodriguezferna]

You can use this example:

First 'dummy' CDS with data from Current Month:

@AbapCatalog.sqlViewName: 'ZZZJC_004A_V'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'ZZZJC_004Current'
define view ZZZJC_004Current 
as 
select from t000 as Dummy 
{
      key '1' as GLAccount
    , key 'S' as VendorName  
    , key ' ' as BA  
    , key ' ' as TradingPartner  
    , key 'Z' as CustomerName
    
    , 10 as Amount    
}
where 
Dummy.mandt = $session.client

union select from t000 as Dummy { key '2' as GLAccount, key ' ' as VendorName, key ' ' as BA, key 'T' as TradingPartner, key ' ' as CustomerName, 20 as Amount } where Dummy.mandt = $session.client
union select from t000 as Dummy { key '3' as GLAccount, key ' ' as VendorName, key ' ' as BA, key ' ' as TradingPartner, key ' ' as CustomerName, 30 as Amount } where Dummy.mandt = $session.client
union select from t000 as Dummy { key '4' as GLAccount, key 'W' as VendorName, key ' ' as BA, key ' ' as TradingPartner, key ' ' as CustomerName, 40 as Amount } where Dummy.mandt = $session.client
union select from t000 as Dummy { key '5' as GLAccount, key 'X' as VendorName, key ' ' as BA, key 'Z' as TradingPartner, key ' ' as CustomerName, 50 as Amount } where Dummy.mandt = $session.client

Second 'dummy' CDS with data from Prior Month:

@AbapCatalog.sqlViewName: 'ZZZJC_004B_V'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'ZZZJC_004Prior'
define view ZZZJC_004Prior
as 
select from t000 as Dummy 
{
      key '1' as GLAccount
    , key 'S' as VendorName  
    , key ' ' as BA  
    , key ' ' as TradingPartner  
    , key ' ' as CustomerName
    
    , 11 as Amount  
    
}
where 
Dummy.mandt = $session.client
union select from t000 as Dummy { key '2' as GLAccount, key ' ' as VendorName, key ' ' as BA, key 'T' as TradingPartner, key ' ' as CustomerName, 22 as Amount } where Dummy.mandt = $session.client
union select from t000 as Dummy { key '4' as GLAccount, key 'W' as VendorName, key ' ' as BA, key ' ' as TradingPartner, key ' ' as CustomerName, 33 as Amount } where Dummy.mandt = $session.client
union select from t000 as Dummy { key '5' as GLAccount, key 'X' as VendorName, key ' ' as BA, key 'Z' as TradingPartner, key ' ' as CustomerName, 44 as Amount } where Dummy.mandt = $session.client
union select from t000 as Dummy { key '6' as GLAccount, key 'U' as VendorName, key ' ' as BA, key 'Y' as TradingPartner, key ' ' as CustomerName, 55 as Amount } where Dummy.mandt = $session.client

CDS Merge data:

@AbapCatalog.sqlViewName: 'ZZZJC_004C_V'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'ZZZJC_004Merge'
define view ZZZJC_004Merge 
as 
// Inner join
select from ZZZJC_004Current as Current
    
    inner join ZZZJC_004Prior as Prior
        on Current.GLAccount      = Prior.GLAccount
       and Current.VendorName     = Prior.VendorName    
       and Current.BA             = Prior.BA            
       and Current.TradingPartner = Prior.TradingPartner
       and Current.CustomerName   = Prior.CustomerName  

{
      key Current.GLAccount
    , key Current.VendorName    
    , key Current.BA            
    , key Current.TradingPartner
    , key Current.CustomerName
    
    , Current.Amount as CurrentAmount   
    , Prior.Amount   as PriorAmount   

}

union

// Left Outer join
select from ZZZJC_004Current as Current
    
    left outer join ZZZJC_004Prior as Prior
        on Current.GLAccount      = Prior.GLAccount
       and Current.VendorName     = Prior.VendorName    
       and Current.BA             = Prior.BA            
       and Current.TradingPartner = Prior.TradingPartner
       and Current.CustomerName   = Prior.CustomerName  

{
      key Current.GLAccount
    , key Current.VendorName    
    , key Current.BA            
    , key Current.TradingPartner
    , key Current.CustomerName
    
    , Current.Amount as CurrentAmount   
    , 0              as PriorAmount   
}
where
    Prior.GLAccount is null
    
union

// Right Outer join
select from ZZZJC_004Current as Current
    
    right outer join ZZZJC_004Prior as Prior
        on Current.GLAccount      = Prior.GLAccount
       and Current.VendorName     = Prior.VendorName    
       and Current.BA             = Prior.BA            
       and Current.TradingPartner = Prior.TradingPartner
       and Current.CustomerName   = Prior.CustomerName  

{
      key Prior.GLAccount
    , key Prior.VendorName    
    , key Prior.BA            
    , key Prior.TradingPartner
    , key Prior.CustomerName
    
    , 0            as CurrentAmount   
    , Prior.Amount as PriorAmount   
}
where
    Current.GLAccount is null    

[former_member607480]

Hi Juan Carlos,

Thank you so much for such a detailed quick response - I really appreciate it!

I have used your code and tailored it to my needs (i.e. to include the use of parameters).

The result I now get is a merged table with all of the rows (364) from both Current (57 rows) and Prior (307 rows). The values for Current & Prior now show in different columns which is closer than I have ever got (as below).

However, if we focus on the account 0010010290 as an example (see below) - the bottom two lines highlighted in red have the exact same data in the first 5 columns but have been presented as separate lines (essentially separating the Current Year entry and Prior Year entry).

Do you happen to know if there is a tweak in the CDS code that we can make to have entries such as these appear on the same line? Example below:

Thanks again in advance.

Pete

[jrodriguezferna]

Review the values of both CDS, sometime, the data browser can lied you, because represent 'null' value as a 'blank' value. Remembers, now, you are working direclly with the DB. Check this Link: ABAP CDS - coalesce

You can check this in other CDS/SQL Console:

Select 
GLAccount, 
COALESCE( VendorName, 'X' ) as MyVendorNameCheck,
COALESCE( BusinessAres, 'X' ) as MyBusinessAreaCheck,
.......
from CDSPrior/CDSCurrent
where GLAccount = '0010010290'

[DoanManhQuynh]

What happend if you do like that:

select from database
{    key'1'as GLAccount
    ,key'S'as VendorName  
    ,key' 'as BA  
    ,key' 'as TradingPartner  
    ,key'Z'as CustomerName    
    ,sum( 10 ) as current_Amount    
    ,0 as prior_Amount
}
union all
{
      key '1' as GLAccount
    , key 'S' as VendorName  
    , key ' ' as BA  
    , key ' ' as TradingPartner  
    , key ' ' as CustomerName
    , 0  as current_Amount
    , sum( 11 ) as prior_Amount  
    
}
group by keys...

i dont have system to check now...

[dmitry_kuznetsov1]

Based on your example what you actually need is a UNION ALL (so it both performs and does not remove the duplicates) with 7 columns as an output. Nothing special with first 5 columns, but the other two are a bit more special:

For the first part of a union-all you need to produce:

• a new column AmountLC as CurrentAmt
• an empty column '' as PriorAmount

For second part of union-all you need vice versa

• an empty column '' as CurrentAmt
• a new column AmountLC as PriorAmount

Hope it helps

------------

Dmitry Kuznetsov

BI Consulting

[former_member607480]

Hi Dmitry, thank you very much for your reply - much appreciated.

Thanks.
Pete