220
edits
Changes
Jump to navigation
Jump to search
→Joining companybasekeys with maskeys and ipokeys
==Joining companybasekeys with maskeys and ipokeys==
Before doing this stage make sure the following is true:
#companybasecore and , mascore , ipocore are clean core tables...They should be 1:1 on themselves. That means 1 key should match to one row in each respective table. See [[#Cleaning the Companybase table|Cleaning the Companybase table]] [[#Cleaning mas table|Cleaning mas table]] [[#Cleaning ipos table|Cleaning ipos table]] for instructions
#You've done name based matching on the keys in companybasecore and mascore and cleaned up the matcher output file. See [[#Name Based Matching companybase keys to mas keys|Name Based Matching companybase keys to mas keys]] and [[#Fixing Errors in the Matcher Output for portco and mas|Fixing Errors in the Matcher Output for portco and mas]]
#You've done name based matching on the keys in companybasecore and ipocore and cleaned up the matcher output file. See [[#Name Based Matching companybase keys to ipo keys|Name Based Matching companybase keys to ipo keys]] and [[#Fixing Errors in the Matcher Output for portco and ipo|Fixing Errors in the Matcher Output for portco and ipo]]
We want to join the three sets of keys together before grabbing other data from their respective tables because there will be collisions with the maskeys and ipokeys. Some companies will have ipos as well as mergers/acquisitions or the data might also be miss coded by SDC platinum. The problem for us is a company that has both an ipo and ma will cause our join row counts to increase every time we join with these duplicate keys. We want a portcokey to join with only one ipokey or maskey in our master table. Running the query below creates a table that contains the three sets of keys:
