topic Adding a flag in a script in QlikView

Adding a flag in a script

Mon, 23 Sep 2013 14:32:20 GMT

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!

Re: Adding a flag in a script

swuehl — Mon, 23 Sep 2013 14:50:00 GMT

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;

Re: Adding a flag in a script

srchilukoori — Mon, 23 Sep 2013 15:02:31 GMT

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.

Re: Adding a flag in a script

Mon, 23 Sep 2013 16:17:38 GMT

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;