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 ) ) )

---

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

---

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

---

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

---

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

---

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

---

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

---