Skip to main content

Hi together, there's spaghetti in my head. I have the feeling like it should be possible, but can't figure out how to.

I've a table like this

A

B

0

NULL

1

0

2

1

3

1

4

1

5

1

Column A contains the case key, B can contain a case key as a reference. E.g. case 1 refers to case 0. And the cases 2, 3, 4, and 5 refer to case 1.

 

Now I want to "turn around" the reference so that there is a new column that shows e.g. for case 1 that it's referenced by 2, 3, 4, and 5. The result should look like this:

A

B

C

0

NULL

1

1

0

2, 2, 3, 4

2

1

NULL

3

1

NULL

4

1

NULL

5

1

NULL


Any ideas?
 

To create a new column that shows which cases are referencing a given case, follow these steps:

  1. Group the table by the ReferenceKey (i.e., Column 😎 to identify each case that is being referenced.

  2. Use an aggregation function such as COLLECT_LIST on the CaseKey (Column A) within each group to gather all cases that reference the same ReferenceKey.

  3. This operation generates a mapping where each ReferenceKey is associated with a list of all CaseKey entries that point to it.

  4. Finally, join this mapping back to the original table on the CaseKey to create a new column (e.g., ReferencedBy) which lists all referencing cases.

This approach efficiently reverses the original reference relationship and enriches the dataset with back-referenced information.

 

Thank you ​@Siddharth_Gupta. Now I realized, that I forgot to mention, I’m an analyst “only”. Is there a PQL-way too?

Reply


Community Code of Conduct