Hello,
I am looking for something similar to a lookup function in Celonis. Is it something possible?
I have a child table "X" linked to Parent table "A" another child table "Y" linked to same parent table "A". Both links are based on different keys. I want to lookup a value from X to Y is it something possible as in is there PQL formula which could help me do that?
Thanks.
I am not sure of a particular Look Up formula, but I have a solution that can help you identify values in A that have values in X and Y.
CASE WHEN PU_COUNT_DISTINCT(A, X.KEY) > 0 AND PU_COUNT_DISTINCT(A, Y.KEY) > 0 THEN 'A Linked to X and Y'
WHEN PU_COUNT_DISTINCT(A, X.KEY) > 0 AND PU_COUNT_DISTINCT(A, Y.KEY) = 0 THEN 'A Linked to X'
WHEN PU_COUNT_DISTINCT(A, X.KEY) = 0 AND PU_COUNT_DISTINCT(A, Y.KEY) > 0 THEN 'A Linked toY'
ELSE 'No link from A to X or Y
END
This will allow you to identify the A values that have linkage.
I am not sure of a particular Look Up formula, but I have a solution that can help you identify values in A that have values in X and Y.
CASE WHEN PU_COUNT_DISTINCT(A, X.KEY) > 0 AND PU_COUNT_DISTINCT(A, Y.KEY) > 0 THEN 'A Linked to X and Y'
WHEN PU_COUNT_DISTINCT(A, X.KEY) > 0 AND PU_COUNT_DISTINCT(A, Y.KEY) = 0 THEN 'A Linked to X'
WHEN PU_COUNT_DISTINCT(A, X.KEY) = 0 AND PU_COUNT_DISTINCT(A, Y.KEY) > 0 THEN 'A Linked toY'
ELSE 'No link from A to X or Y
END
This will allow you to identify the A values that have linkage.
Thanks @john.brendan.owens will looking into this!
You can also try the function MULTI_IN.
https://help.celonis.cloud/help/display/CIBC/MULTI_IN
Tks.
Dan.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
