Mapping load across multiple tables

Tue, 26 Jan 2010 00:43:38 GMT

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!

Mapping load across multiple tables

johnw — Tue, 26 Jan 2010 02:02:44 GMT

Maybe this?

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
;

topic Mapping load across multiple tables in QlikView

Mapping load across multiple tables

Mapping load across multiple tables