LEFT JOIN results in duplicate rows - looking for Vlookup functionality or ApplyMap solution
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 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:
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.