Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Katherine
New Contributor II

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, 

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?

1 Solution

Accepted Solutions
Katherine
New Contributor II

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

 

View solution in original post

2 Replies
Partner
Partner

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

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
New Contributor II

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

 

View solution in original post