Skip to main content
Solved

PU_COUNT_DISTINCT - No Common parent between tables could be found

  • June 29, 2020
  • 7 replies
  • 22 views
thomas.keim
Level 10
Forum|alt.badge.img+2

Hello,

Im looking for an solution for this challenge:

We have 3 SAP ERP Tables

VTTK (Shipments)

VTTP (Shipment Items)

LIKP (Deliveries)

LIPS (Delivery Items)

VTTK is linked 1:N with VTTP

VTTP is linked 1:1 with LIKP

LIKP is linked 1:N with LIPS

I want to count the distinct material numbers per shipment.

Count per delivery works fine:

PU_COUNT_DISTINCT(LIKP, "LIPS"."MATNR")

But I need it per shipment for further calculating KPIs, like this:

PU_COUNT_DISTINCT(VTTK, "LIPS"."MATNR")

Here I get the following error:

No common parent between tables could be found - Please check your schema. The tables VTTK und LIPS do not have a common parent in the schema

Within the help I found the BIND function. I tought, this can be the solution:

PU_COUNT_DISTINCT(VTTK, BIND(LIKP, "LIPS"."MATNR"))

But here I get an error in the formula.

How can I solve this with a formula und without changing the data schema.

Thanks for you help

Thomas

Best answer by david.beche12

Hi Thomas,

ah, yeah, we need to sum up each value from the PU_COUNT_DISTINCT only once in the outside PU function. We can do this by using INDEX_ORDER:

PU_SUM("VTTK", LEN("VTTP"."TKNUM") * 0 + PU_COUNT_DISTINCT("LIKP", "LIPS"."MATNR"), INDEX_ORDER( "VTTP"."TKNUM", GROUP("VTTP"."LIKP_FK") ) = 1 )

VTTP.LIKP_FK here is the foreign key column in VTTP. If the foreign key consists of multiple attributes, you need to define all of them in the GROUP() option of the INDEX_ORDER.

Best

David

7 replies

S
Level 3
  • sion.oppen
  • Level 3
  • 15 replies
  • July 1, 2020
Hi MajorTom, Unfortunately I work with a version of Celonis that doesnt have the BIND functionality, but if I understand correctly it pulls a column from the parent table to the child table. I cant test if it works, but perhaps you can make the formula work as follows:
  1. Pull Material from LIPS to LIKP
  2. Pull Material from LIKP to VTTP
  3. Perform distinct count for VTTK on Material in VTTP
PU_COUNT_DISTINCT( VTTK, BIND ( VTTP , BIND ( LIKP , LIPS.MATNR ) ) )
T
Level 3
Hello, I have the very same problem as MajorTom. I have already created a support ticket for this problem, but didnt get an answer. Maybe one of the Celonis guys could take a look at this topic? BR Thomas
D
Level 1
Forum|alt.badge.img+2
  • david.beche12
  • Level 1
  • 240 replies
  • July 8, 2020
Hi @MajorTom, It looks like the reason why PU_COUNT_DISTINCT("VTTK", "LIPS"."MATNR") does not work is the 1:1 relationship in the middle of your join path. In Celonis, 1:1 relationships are special 1:N relationships, meaning that one side is regarded as the N side of the join. If PU_COUNT_DISTINCT("VTTK", "LIPS"."MATNR") does not work, I would assume that VTTP is regarded as the N side in the 1:1 relationship. In that case, for example PU_COUNT("LIKP"."VTTP"."Column") works, but PU_COUNT("VTTP"."LIKP"."Column") does not. If it was LIKP on the N side, PU_COUNT_DISTINCT("VTTK", "LIPS"."MATNR") should work. You can change this in the Data Model Editor in Event Collection by flipping the source and target tables of that foreign key. After that, you need to reload the Data Model. PU_COUNT_DISTINCT(VTTK, "LIPS"."MATNR") should work then. @nagelt Please check if this is also fixes the issue you are facing. Best David
T
Level 3

Hello David,

thank you for the response. The table VTTP is normally on the N side of the join. The whole table structure normally looks as follows:

VTTK (Child 1) --> (1:N) --> VTTP (Parent 1) --> (N:1) --> LIKP (Child 2) --> (1:N) --> LIPS (Parent 2)

So the formula PU_COUNT_DISTINCT(VTTK, LIPS.MATNR) will not work since LIPS is not a parent of VTTK. Instead, the idea is to first pull the number of distinct materials from table LIPS to LIKP with

PU_COUNT_DISTINCT(LIKP, LIPS.MATNR)

which works fine.

After that the result should be attached to table VTTP with

BIND(VTTP, PU_COUNT_DISTINCT(LIKP, LIPS.MATNR))

Finally the distinct number of materials per shipment should be calculated with

PU_COUNT_DISTINCT(VTTK, BIND(VTTP, PU_COUNT_DISTINCT(LIKP, LIPS.MATNR)))

However each time the BIND formula is involved, the system displays Parsing error in function BIND even though the syntax is used as described in the documentation.

Is there something wrong with the syntax of BIND? Or does BIND only work with certain Celonis versions? We are using the Celonis hybrid cloud in the IBC. Any help with this would be very much appreciated.

Best regards

Thomas

D
Level 1
Forum|alt.badge.img+2
  • david.beche12
  • Level 1
  • 240 replies
  • July 8, 2020

Hi Thomas,

in the (full) IBC, your query would work (except I think you would need to use a PU_SUM as the outside PU function).

For Hybrid IBC, the BIND function is not yet included, and will probably be included in the next Hybrid release.

Until then, you can hack the BIND behavior by introducing the join between LIKP and VTTP, for example by adding INT columns of both tables:

PU_SUM ( "VTTK", "VTTP"."IntColumn" * 0 + PU_COUNT_DISTINCT("LIKP", "LIPS"."MATNR") )

if you dont have an INT column in VTTP, you can also use LEN(VTTP.StringColumn) to get an INT. It doesnt metter as you multiply it with 0 anyways, but it will introduce the join between the two tables.

Best

David

thomas.keim
Level 10
Forum|alt.badge.img+2
  • thomas.keim
  • Author
  • Level 10
  • 9 replies
  • July 8, 2020

Hello David,

I have tested your proposal in the following way

PU_SUM("VTTK", LEN("VTTP"."TKNUM") * 0 + PU_COUNT_DISTINCT("LIKP", "LIPS"."MATNR"))

But instead of 2 distinct material numbers in the shipment (VTTK), I get 5.

This depends because the count is working on VTTP or LIKP level.

(The materials are available in almost all LIKP items > LIPS .)

I need the distinct count of LIPS.MATNR for the whole VTTK shipment.

Kind regards

Thomas

D
Level 1
Forum|alt.badge.img+2
  • david.beche12
  • Level 1
  • 240 replies
  • Answer
  • July 8, 2020

Hi Thomas,

ah, yeah, we need to sum up each value from the PU_COUNT_DISTINCT only once in the outside PU function. We can do this by using INDEX_ORDER:

PU_SUM("VTTK", LEN("VTTP"."TKNUM") * 0 + PU_COUNT_DISTINCT("LIKP", "LIPS"."MATNR"), INDEX_ORDER( "VTTP"."TKNUM", GROUP("VTTP"."LIKP_FK") ) = 1 )

VTTP.LIKP_FK here is the foreign key column in VTTP. If the foreign key consists of multiple attributes, you need to define all of them in the GROUP() option of the INDEX_ORDER.

Best

David


Community Code of ConductAccessibility statement