Adding a flag in a script

[#1]

Hi,

I have a Patients table and Clinique Visits table.

Not all patients have visits records.

I’d like to add a flag to the Patients table

That represents when no visits exists for the relevant patients.

The following left join doesn’t work.

Left Join (Patients)

LOAD

PatientID,
'No' as WithClnVisits

Resident [Clinique Visits]
Where not Exists (PatientID);

Any idea why?

Thanks!

[swuehl · Accepted solution]

Try like this:

Visits:

LOAD
PatientID as PatientVisitID
Resident [Clinique Visits];

Patients:

LOAD

     PatientID,

     if(exists(PatientVisitID, PatientID),'Yes','No') as WithClnVisits,

     ...

FROM PatientsTable.;

Drop table Visits;

[srchilukoori]

You need to do an inner Join with the clinic visits first, and add a flag 'Yes'. Then concatenate the Resident PatientID table with Where not exists condition.

[#4]

Hi,

Thanks a lot!

Never saw this syntax before 🙂

I managed to achieve the desied result with using the condition in a left join:

Left Join (Patients)

LOAD

     PatientID,

     if(exists(QlnPatientID, PatientID),'Yes','No') as WithClnVisits

Resident Patients;