add records from second table to the main table

[Sam_1985]

I need to add records from second table to the main table but I want to add condition if its not in main table then add it

the error appear that the PAT_EST_ID field not there

first I do this mapping load

PATEST:
Mapping LOAD
PATIENT_ID &'|'& ESTABLISHMENT_CODE as PAT_EST_ID,
PATIENT_KEY
Resident PATIENT;
Drop Table PATIENT;

this is the main table

F_DEATH:
LOAD
'CBDR' as FLAG,
NOTE_ID,
NOTE_CREATED_DATE,
NOTE_CURR_STATUS,
NOTE_AUTH_DATE,
NOTE_LAST_UPDATED_DATE,
BIRTH_ID,
HOSPITAL_REF_NO,
NOTIFY_INST_ID,
HOSPITAL_REF_NO &'|'& NOTIFY_INST_ID as PINST_ID;

This is the second table

Concatenate(F_DEATH)
LOAD
'S' as FLAG,
ENCOUNTER_KEY,
ENCOUNTER_CATEGORY_KEY,
ENCOUNTER_TYPE_KEY,
ENCOUNTER_STATUS_KEY,
REFERRAL_ROUTE_KEY,
DATE_KEY,
TIME_KEY,
PATIENT_KEY,
// PATIENT_KEY AS MORTALITY_PATIENT_KEY,
LOCATION_KEY,
AGE_KEY,
ESTABLISHMENT_KEY,
DEPARTMENT_KEY,
CLINIC_KEY,
EMPLOYEE_KEY,
DEATH_NOTE_DATE_KEY,
DEATH_ENCOUNTER_KEY,
//DEATH_DATE_KEY,
DEATH_TIME_KEY,
APPLYMAP('MAPTEST',DEATH_DATE_KEY,'UNKNOWN') AS DEATH_DATE,
if(PLACE_OF_DEATH_KEY=70 ,'HOSPITAL',
if(PLACE_OF_DEATH_KEY=71 ,'BROUGHT DEAD')) AS PLACE_OF_DEATH_KEY,
// PLACE_OF_DEATH_KEY,
PARENT_INSTITUTE_KEY,
DIAGNOSIS_KEY as DIAGNOSIS_DEATH,
DIAGONOSIS_CLASSIFICATION_KEY,
CAUSE_OF_DEATH_YN_KEY,
ICU_CAUSE_OF_DEATH_KEY,
DISCHARGE_RESULT_KEY,
SOURCE_TABLE_KEY,
QUANTITY,
PATIENT_MINI_KEY,
// CAUSE_OF_DEATH_KEY
if(CAUSE_OF_DEATH_KEY=89 ,'RTA',
if(CAUSE_OF_DEATH_KEY=90 ,'OTHER EXTERNAL CAUSES (NON-RTA)',
if(CAUSE_OF_DEATH_KEY=91 ,'NATURAL DEATHS'
))) AS CAUSE_OF_DEATH_KEY,
Applymap('PATEST',PATIENT_ID &'|'& ESTABLISHMENT_CODE,'UNKNOWN') as PAT_EST_ID
FROM [lib://Qvd's/CBDR/F_MORTALITY.qvd]
(qvd) WHERE (PAT_EST_ID) <> PINST_ID ;

[Sam_1985]

HOSPITAL_REF_NO, this is patient id
NOTIFY_INST_ID, this is establishment code

[Iswarya_]

Hi @Sam_1985 ,

in the second table you can take a preceding load as :

Load *

where not exists(PINST_ID,PAT_EST_ID)

[Sam_1985]

now the issue is the errors appear while using apply map in the table when I do apply map this error appear

[Sam_1985]

this is the mapping load

ID:
Mapping LOAD
PATIENT_ID,
ESTABLISHMENT_CODE
Resident PATIENT;

this is the apply map i used in table

APPLYMAP('ID', PATIENT_ID) AS PID,

[Iswarya_]

@Sam_1985 Make sure that the PATIENT_ID field is available in the QVD [lib://Qvd's/CBDR/F_MORTALITY.qvd]
(qvd) as you could see the error appears as 'Field is not found'

[Sam_1985]

yes is not there but i want to add it to this table by apply map??/////

[Sam_1985]

but I don't know how

[Iswarya_]

Applymap works if there is a common key available between both the tables so comparison is made between the common fields from both the tables to match its field values

Mapping Table and ApplyMap Function – QlikviewDiary (wordpress.com)