Hi all. I have a question regarding a specific mapping problem. So say I have a table called Projects with a field ProjectId. I've got a table called Tasks with fields TaskId and Priority. Priority can be High, Med, Low. I've also got a link table between them called Link with fields ProjectId and TaskId.
I want to create a new field in the Projects table called HighestTaskPriority, which takes all the Tasks that link to a given Project and finds the highest priority of all those tasks. So if Project A links to Tasks 1,2,3 and their respective priorities are high, medium, medium, Project A will have HighestTaskPriority equal to High. Does this make sense?
Does anyone have a good solution for this? I was thinking to use mappings to do this. So I'd have one mapping for each priority and do a nested if statement to pick the highest one for each project.
Please let me know if you have any ideas. Thank you!
LEFT JOIN ([Link]) LOAD TaskId ,match(Priority,'Low','Medium','High') as NumericPriority RESIDENT [Tasks] ; LEFT JOIN ([Projects]) LOAD ProjectId ,pick(max(NumericPriority),'Low','Medium','High') as HighestTaskPriority RESIDENT [Link] GROUP BY ProjectId ; DROP FIELD NumericPriority ;
