Create a new field based on 2 separate fields from 2 different tables

[sharon9]

I have 2 separate tables.  During LOAD process, I want to create a new field (OVERALL_STATUS) where it contains the PROGRESS values from table1 but if the PROGRESS = Started, to replace  with values from HEALTH from table2.  See output. 

Not sure if I need to create a new table or if there's an efficient way to create the new field and add new field to the STATUS_REPORT table.  There is a PROJECT_ID that is used to establish the relationship.

table1: PROJECT_DETAILS : field: PROGRESS  values: Started Not Started Completed

table2: STATUS_REPORT: field: HEALTH values: On Track Needs Help At Risk

Output: table?? field: OVERALL_STATUS: values: On Track Needs Help At Risk Not Started Completed

[Digvijay_Singh]

Not sure if I understand the problem completely but in such situations mapping tables are very helpful, you may need to create mapping table using Qlik 'Mapping Load' script prefix, it can have just two fields, which I think would be Project ID and PROGRESS. While loading table2 you need to create new field OVERALL_STATUS based on what you get when use applymap statement.

Thanks,

DJ