Can I join tables which don't have connection?

Hello, have you check PU function? Source https://docs.celonis.com/en/pull-up-aggregation.html, also part of https://academy.celonis.com/learn/learning-path/build-analyses-advanced track.

Hi @aldona.potoc, The Bind functionality might be the solution for you: BIND (celonis.com).

Hi Jan,

thanks for this advice, I've tried PU_MAX (A, BIND (C , E)) [A, C and E are my tables shown on attached scheme] but it didn't work, I got following error message What am I doing wrong?

Hi @aldona.potoc,

I checked your case for a bit longer and think indeed that Bind cannot solve it. I draw the situation as I understood it:

It makes sense that you can connect A&C and E&C separately. Namely, from A there is only one C that's possible and for E to C this is the same. However, what you are trying to do is to connect A to E.

Therefore, you have to return one row of E for each C. The difficulty here is table D. You'll need a nested PU function, to first determine which row/value for D should be considered, and then need a second PU to do the same between D and E.

As far as I know, it is not possible to do a nested PU-function, which makes sense in my opinion (if you do a PU_MAX on Table D below, you would get row 3 or 4, but how to determine which row of table E should be returned?)

It's a bit hard to validate my ideas above without having the underlying data, so please let me know if you think differently!

Kind regards,

Jan-peter

Hi @janpeter.van.d

you understood the situation very well, the scheme is exactly as described in your post.

I applied PU function and now I have A & C & E data in one OLAP table. I used PU_LAST(C, E.FN, E.LOAD = PU_LAST(C, E.LOAD, ORDER BY E.LOAD)) in order to have E.FN values based on the last timestamp from E table (E.LOAD). So it looks like D is not necessary. However my goal it to have a below result

as you can see, E.FN returns the same value in both rows and E.FD is missing. E.FD is needed to determine E.FN. I tried to apply the below code, no success. Apparently I am making a mistake but I don't know how to fix it. Do you have any idea?

PU_LAST(C, E.FN, E.LOAD = PU_LAST(C, E.LOAD, ORDER BY E.LOAD))

AND

PU_LAST ((C, E.FD, E.LOAD) = A.FD))

Best regards,

Aldona

Hi Aldona,

Based on the relations you have, tables A and E do not see each other. So my suggestion would be to change the `A.FD` part with the same PU-statement as you have in your table already in this part: PU_LAST ((C, E.FD, E.LOAD) = A.FD)). The result will be something like:

PU_MAX(C, E.FD, E.LOAD = PU_xx(C, A.FD)). Honestly, I'm not sure if PU-functions are allowed in the filter statement, that would be an interesting experiment.

Hope this helps!

Kind regards,

Jan-peter

Hi Jan-peter,

unfortunately no result :( However in the meantime I was informed that my colleagues will work on data model reconnection and table join so maybe the root cause of this issue will be solved soon. Thank you once again for your help and involvement :)

Best regards,

Aldona

You're welcome!