cancel
Showing results for 
Search instead for 
Did you mean: 

Join causing my values going crazy in datasphere

fraita
Explorer
0 Kudos

Hi,

I have two tables.
RSEG
EKPO

I want to join these tables, so I can receive the Ordered value (BRTWR, found in EKPO) and Invoiced Value (WRBTR, found in RSEG) and compare it.

The problem is that RSEG has about 50k rows and EKPO has about 14k.
I join them by Purchasing Document Number (EBELN)

I've tried different Join Type, but I thought Left (to RSEG) was the most exact one.
Problem that I've encountered is that EKPO is trying to put their total Ordered value on multiple rows which, of course, leads to a crazy amount.

How should I think when doing this kind of cleaning?

I would say that RSEG is more "integrated"/relevant with the rest of the tables.

Accepted Solutions (0)

Answers (3)

Answers (3)

fraita
Explorer
0 Kudos

I guess a Group Function for my table would work too. I need the total invoice spend for that Document Number and then I can combine it with other tables.

raymond_giuseppi
Active Contributor
0 Kudos

You could try

  • Add key field EBELP not only EBELN in your JOIN
  • Use table EKBE as main table (filter on VGABE = 2 or 3) and left join EKPO, RSEG for missing fields with full primary key
fraita
Explorer
0 Kudos

Hmm, Tried to go this way but it doesn't work 😕 Still same problem that I have multiple rows with same Purchasing Document Number in EKBE and only one of the same in EKPO. It ends up that I have multiple rows with the same Gross Order Value.



Gross Value should be 2,100,000.00. But only on one row.
450000006 has like 30 rows nu.

Could it be that I use wrong Cardinality? What would be the best one to use?

raymond_giuseppi
Active Contributor
0 Kudos

Add EBELP (item number) as already written

fraita
Explorer
0 Kudos

Doesn't help 😕



Still 45000006 get multiple rows with "2100000" in Gross order value.
Multiply that with around 30 rows and we get a really big Gross order value.

irodin10
Explorer
0 Kudos

Hi Martin,

this is probably due to either one or both of the following issues in your modeling:

a) wrong join definition,

b) wrong cardinality settings.

For b) take a look at https://blogs.sap.com/2019/04/05/introduction-of-join-cardinalities-in-sql-with-sap-hana-2.0-sps04/ - this applies to datasphere as well.

For a) there can be a variety of mistakes here, but what I usually see for e.g. left joins is that the developer is not using all (true) primary keys of the right-side table in the join condition and therefore the granularity is not matching. If you cannot match up all the primary keys you need to filter/aggregate the right-side table accordingly. It's a complicated topic to explain, but I hope that hint will help you get the general direction.

Best Regards,

Irvin.

fraita
Explorer
0 Kudos

Yeah, It feels like I'm doing something wrong but I'm not sure of what. Tried to play around but I don't know... Feels kind of "set" when it comes to blending data.