Join challenge

[#1]

Hi All,

I have a table called Patients with the following fields:

PatientID,

LastName,

FirstName,

InsuranceID,

Balance

There isanother table called Collector with the following fields:

InsuranceID,

CollectorName,

PatientRange – This column contains range using the first character of  Patient’s Last Name. For example, A – C,  which means this collector is responsible forpatients whose last name begins with A B or C..

Any idea howto join the two table? The join should include InsuranceID and Patient name/range.Thanks in advance for you help!

[Jason_Michaelides]

Does every patient have an InsuranceID? And are all InsuranceIDs in the second table? If so, just let QlikView do the join on InsuranceID for you.

Jason

[#3 · Accepted solution]

Try something like this:

Paitient:

InsuranceID & '-' & left(LastName,1) as link

Collector:

InsuranceID & '-' & PatientRange as link

Edit:

Just saw that the field holds A-C not A B C.

What you could do is create a flag depending on the ranges. If you have a fixed amount you can do an if statement to do like

if(PatientRange='A-C',1,

if(PatientRange ='D-F',2,

ETC.

and do match in the paitent table like

if(match( left(LastName,1),'A','B','C'),1

if(match( left(LastName,1),'D','E','F'),2