Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
baylor2016
Contributor

LEFT JOIN (or LEFT KEEP) returns too many records

HI,

I read this post (Understanding Join, Keep and Concatenate) and thought it it easy to get the records by using LEFT JOIN or LEFT KEEP. It turned out that it is not easy. The LEFT JOIN (or KEEP) returned all the records including the records does not match.  What is my problem?

I this one in first script tab.

Encounters:

with DiabetesPatientList as (     

select distinct a.pat_id

from problem_list a          

where a.icd9_code like '250.%'

)

select a.pat_id, a.PAT_ENC_CSN_ID,a.Contact_date, a.PCP_PROV_ID, a.FIN_CLASS_C,  a.VISIT_PROV_ID, a.VISIT_PROV_TITLE,

a.BMI, a.BP_SYSTOLIC, a.BP_DIASTOLIC,  e.DEPARTMENT_NAME,

trunc ((a.contact_date - b.birth_date)/365.25) as "EncounterAge",

c.Prov_name as PCP,

d.prov_name as VisitPhysician,

e.Department_NAME as Department,

extract(Year from a.contact_date) as intYear, extract(Year from a.contact_date)*100+extract(month from a.contact_date) as IntYearMonth

from pat_enc a 

inner join patient b on a.pat_id = b.pat_id

INNER JOIN DiabetesPatientList f on f.pat_id=a.pat_id

LEFT JOIN clarity_SER c on a.PCP_PROV_ID =c.prov_id    

left join clarity_ser d on d.prov_id = a.visit_prov_id

left join clarity_dep e on a.DEPARTMENT_ID = e.DEPARTMENT_ID       

where a.CONTACT_DATE >= to_date('01-Jan-2011', 'dd-mon-yy') and      

(a.DEPARTMENT_ID in (1711521800,1710021800)

and a.APPT_STATUS_C=2

and a.ENC_TYPE_C =101;

And the script in the second tab:

Patient:

LOAD *;

left join (Encounters)

sql

select  a.pat_id, a.PAT_NAME, a.PAT_MRN_ID, a.Birth_Date, a.CUR_PCP_PROV_ID, g.name as Sex

from patient a

left join ZC_SEX g on g.RCPT_MEM_SEX_C=a.sex_c;

6 Replies

Re: LEFT JOIN (or LEFT KEEP) returns too many records

Left Join and Left Keep leave all the records from the left table. In your case that means all the records of the Encounters table.

In addition a Left Join can create more records if for a single record in the left table there are several matching records in the right table.

Table1:

LOAD * INLINE [

A, B

1, 1

2, 2

];

JOIN (Table1)

LOAD * INLINE [

A, C

1, 10

1, 20

2, 30

];

The Result is a table with these records:

A, B, C

1, 1, 10

1, 1, 20

2, 2, 30

Table1 had two records before the left join and has three records after the left join.


talk is cheap, supply exceeds demand
baylor2016
Contributor

Re: LEFT JOIN (or LEFT KEEP) returns too many records

Hi Gysbert,

I know how JOIN AND KEEP work, but it does not work for me.

My problem is that ALL the records are fetched for the right table (Patients). The left table is the encounter table which has multiple records for one patient (right table).  I wanted to get patient list from encounters. Logically it might cause the problem. Please review my code to see whether your can help. Thanks

Re: LEFT JOIN (or LEFT KEEP) returns too many records

It's very simple. If all the records from the right side table are joined to the left table then all the keys from the right side table exist in the left side table. Check your data. I can't do that for you.


talk is cheap, supply exceeds demand
baylor2016
Contributor

Re: LEFT JOIN (or LEFT KEEP) returns too many records

I checked the data. There are only 42,818 encounters (left table), but the right table (patient) returned almost 4 million distinct patient records. Something is not right!

Not applicable

Re: LEFT JOIN (or LEFT KEEP) returns too many records

As per your comments, apply some filters and reduce the rows to less than 100 digits on the first query and check the result.

The other test, join directly on SQL and check how one single query returning ?

baylor2016
Contributor

Re: LEFT JOIN (or LEFT KEEP) returns too many records

I can test more with a smaller size of data. Is the syntax ok? It does not make sense if all the records in right table are returned.

Community Browser