LEFT JOIN results in duplicate rows - looking for Vlookup functionality or ApplyMap solution

[Katherine]

Hi, I'm creating an app to display physician performance compared to benchmark for a number of different quality measures. 

I created a Key Table to prevent synthetic keys that includes three fields:

ProviderIn,

Metric, 

ProviderIn &' - '& Metric as PROVMETRIC

There are two Provider fields in my data - one is just Provider, whereas ProviderIn is a smaller subset of providers that will be included based on a number of criteria (large enough patient denominator, active status, etc). 

The monthly data that will populate the app includes 4 fields:

Provider, 

Metric, 

Clinic, 

Rate

What I want to do is to load the PROVMETRIC field from the Key table, and then pull in the corresponding Clinic and Rate fields from the monthly data. To do this, I created a MonthlyTemp table, then created a Monthly table to pull in PROVMETRIC from Key and LEFT JOIN the fields from the Monthly Temp table.

MonthlyTemp:
LOAD
Provider &' - '& Metric as PROVMETRIC,
Clinic,
Rate 
FROM (location);

Monthly:
LOAD PROVMETRIC
Resident Key;

LEFT JOIN (Monthly)
LOAD Clinic
Rate
Resident MonthlyTemp;

DROP TABLE MonthlyTemp;

However, this results in duplicate rows. What I really want is to use the PROVMETRIC field from the Key table and pull in the other fields like a Vlookup.

Things I've thought about:

1) I tried to use a WHERE ProviderIn=Provider clause but either my syntax is way off or Qlikview just doesn't entertain this kind of scripting

2) I've seen some examples that use Apply Map but I'm not sure how to apply them to my script with the concatenated PROVMETRIC field.

Any suggestions?

[Arthur_Fong]

I guess the PROVMETRIC key is not unique and this key causes a one-to-many relationship from your key table to your Monthly fact table.

Maybe try to distinct the keys for the key table.

Monthly:
LOAD distinct PROVMETRIC
Resident Key;

If problem still occurs please provide a sample data for better understanding.

Thanks and regards,

Arthur Fong

[Katherine · Accepted solution]

I figured this out!

LEFT JOIN wasn't working because Joins only work if the rows match completely, not on the basis of single field match. 

I basically created multiple Apply Maps to use as Vlookup for multiple fields of data:

MonthlyTemp:
LOAD
Provider &' - '& Metric as PROVMETRIC,
Clinic,
Rate 
FROM (location);

NoConcatenate
Key:
LOAD
DISTINCT Provider &' - '& Metric as PROVMETRIC,
ProviderIn,
Metric
Resident (Temp Table);

MonthlyMapDD:

Mapping LOAD

PROVMETRIC,

[Default Department]

Resident MonthlyTemp;

MonthlyMapNum:

Mapping LOAD

PROVMETRIC,

Numerator

Resident MonthlyTemp;

MonthlyMapDen:

Mapping LOAD

PROVMETRIC,

Denominator

Resident MonthlyTemp;

MonthlyMapMonth:

Mapping LOAD

PROVMETRIC,

Month

Resident MonthlyTemp;

MonthlyFact:

LOAD

PROVMETRIC,

ApplyMap('MonthlyMapDD',PROVMETRIC,null())as [Default Department],

ApplyMap('MonthlyMapNum',PROVMETRIC,null())as N,

ApplyMap('MonthlyMapDen',PROVMETRIC,null())as D,

ApplyMap('MonthlyMapMonth',PROVMETRIC,null())as Date

Resident Key;