topic LEFT JOIN Issue in QlikView

LEFT JOIN Issue

Fri, 01 Mar 2013 17:21:46 GMT

I am running into an issue with my LEFT JOINs. When I reload my data nothing is loaded. I am not sure what I need to change in my script. Any help would be greatly appreciated. I have attached a Word File containing my script for you to view.

Listed below are some of my steps to get to where I am at.

LEFT JOINED CDM and Plan table to RevenueAndUsage_Temp in RevenueAndUsage_Temp2 table. This LEFT JOIN worked fine. No issues at this point
Created field in RevenueAndUsage_Temp2 table called "FacPatTypeContract". This was used to link RevenueAndUsage_Temp2 to the Payer Table. This works as long as I am not trying to LEFT JOIN the Payer Table to RevenueAndUsage_Temp2.
Attempted to LEFT JOIN Payer Table to RevenueAndUsage_Temp2 in RevenueAndUsage_Final. This is where my problem occurs. When I try to LEFT JOIN the Payer Table and reload. Nothing gets loaded. Not sure where I go from here.

Thanks for your help.

Brian

Re: LEFT JOIN Issue

Gysbert_Wassenaar — Fri, 01 Mar 2013 17:57:17 GMT

My guess is you should add a noconcatenate for RevenueAndUsage_Final to prevent it being added to RevenueAndUsage_Temp2. When you drop that temp table you are left with nothing.

RevenueAndUsage_Final:
NOCONCATENATE LOAD [Plan Code],
Facility,

...etc

Also, you could replace this

LEFT JOIN
LOAD [CDM Procedure Code],
[Current Price]
FROM
[(7) IU Health - Supply Pricing Data.xlsx]
(ooxml, embedded labels, table is CDM);

with a mapping table and use applymap instead of joining.

CDMPriceMap:

LOAD [CDM Procedure Code],
[Current Price]
FROM
[(7) IU Health - Supply Pricing Data.xlsx]
(ooxml, embedded labels, table is CDM);

//***********Revenue & Usage Temp*************
RevenueAndUsage_Temp:
LOAD [Plan Code],
     Facility,
     [Patient Type],
     [Department Number],
     [CDM Procedure Code],

applymap('CDMPriceMap',[CDM Procedure Code]) as [Current Price]

Volume,

...etc