Hi everyone,
I am analyisng a P2P process and in my data model I have an activity table, which is linked to the tables Vendor and Position via a vendor-/position-number. The Vendor table contains master data for all vendors which I buy stuff from, i.e. one row for each vendor. And the Position table contains master data regarding the purchas-positions I buy, i.e. one row for each purchase-position.
So there is a 1:N relationship between the vendor- / position-table (1) and the activity table (N).
Now I would like to display the amount I pay for all positions bought at one vendor. But if I sum up the values for all positions belonging to a certain vendor i do not get the desired value, but I get the sum for all positions for the vendor multiplied with all activities which have been carried out for this position.
E.g. if I have 2 items I bought from a vendor, each costs 10, the formula does not calculate 20, but 180, as for both items 9 activites have been carried out. (10 * 9 * 2 = 180).
Does anybody know a solution to this problem?
Thanks a lot already in advance!
Greets
Jonas
Page 1 / 1
Hi Jonas,
welcome to the Celonis Community!
The issue here is that a Join between Position / Vendor and the Activity table is executed. So every row in Position and Vendor is joined to all corresponding rows in the Activity table, and therefore, the rows in Position and Vendor are multiplied.
To fix this, I think we should first take a look at your data model. Is there a reason why you join the Vendors to the Activity table? Usually, it is joined to the Position table, because there is one vendor per position, and not per activity.
If you change your data model accordingly, you dont have a join to the Activity table in your query anymore, and thus you should get the result you expect.
Best
David
welcome to the Celonis Community!
The issue here is that a Join between Position / Vendor and the Activity table is executed. So every row in Position and Vendor is joined to all corresponding rows in the Activity table, and therefore, the rows in Position and Vendor are multiplied.
To fix this, I think we should first take a look at your data model. Is there a reason why you join the Vendors to the Activity table? Usually, it is joined to the Position table, because there is one vendor per position, and not per activity.
If you change your data model accordingly, you dont have a join to the Activity table in your query anymore, and thus you should get the result you expect.
Best
David
Hi David,
thank you very much for the solution! I just had some false assumptions in my mind, but it works when joining the vendor and the position table.
Best
Jonas
thank you very much for the solution! I just had some false assumptions in my mind, but it works when joining the vendor and the position table.
Best
Jonas
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.