LEFT JOIN Issue

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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