220
edits
Changes
Jump to navigation
Jump to search
→Joining companybasecore with mascore
--8655
Great! Now you are ready to begin joining the companybase table to the mas table.
==Joining companybasecore companybasekeys with mascoremaskeys and ipokeys==
Before doing this stage make sure the following is true:
#companybasecore and mascore 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]] 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]]
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:
DROP TABLE companybasekeysaddmaskeysaddipokeys;
CREATE TABLE companybasekeysaddmaskeysaddipokeys AS
SELECT c.coname, c.statecode, c.datefirstinv, matcherm.file2targetname AS mastargetname, matcherm.file2targetstatecode AS masstatecode,
matcherm.file2announceddate AS announceddate, matcheri.file2issuer AS ipoissuer, matcheri.file2statecode AS ipostatecode,
matcheri.file2issuedate AS ipoissuedate FROM
companybasecore AS c LEFT JOIN matcherportcomascore as matcherm ON c.coname = matcherm.file1coname AND c.statecode =
matcherm.file1statecode AND c.datefirstinv = matcherm.file1datefirstinv
LEFT JOIN matcherportcoipocore AS matcheri ON c.coname = matcheri.file1coname AND c.statecode = matcheri.file1statecode AND
c.datefirstinv = matcheri.file1datefirstinv;
--44740
\COPY companybasekeysaddmaskeysaddipokeys TO 'companybasekeysaddmaskeysaddipokeys.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
Open
==Name Based Matching companybase keys to ipo keys==
