Hello,
I have an object "ProjectProduct" with fields "Project" and "ProductLine" with data like this :
Project; ProductLine
Project 1; PL4
Project 1 ; PL6
Project 1; PL6
Project 1; PL9
Project 2; PL1
Project 3; PL1
Project 3; PL5
As you can see, a same product line could be displayed several times for a project.
In the recipe, I want to :
1) deduplicate the productLines for same project. In this exemple, PL6 for Project 1 has to be displayed once.
2) For each project, I want to know the number of productline by project :
Project | number of productline
Project 1 | 3
Project 2 | 1
Project 3 | 2
3) For each project, I want to have the list of ProductLine with delimeter ";"
Project | List productlines
Project 1 | "PL4;PL6;PL9"
Project 2 | "PL1"
Project 3 | "PL1;PL5"
Then in my dataset I need these values :
Project | List productlines | number productlines
Project 1 | "PL4;PL6;PL9"| 3
Project 2 | "PL1" | 1
Project 3 | "PL1;PL5" | 2
Thank in advance for your help
Oh - yes, forget the final step:
Use another multi-row formula:
partition by project, sorted by size desc
function row_number() will mark the row with the highest amount of values with 1
finally filter on this row_number = 1
