220
edits
Changes
Jump to navigation
Jump to search
→Creating portcotransactionmaster table
--83
Now you can successfully join the companybasecore table to the ipocore and mascore tables through the companybasekeyipokeycore and companybasekeymaskeycore tables. With this step done you can create a master table which will contain information from companybase and ipo and mas.
==Creating portcotransactionmaster companybaseipomasmaster table==
Before doing this stage make sure you have followed the steps in [[#Joining companybasekeys with maskeys and ipokeys|Joining companybasekeys with maskeys and ipokeys]]
You will be joining the companybasecore table with the mascore and ipocore through the companybasekeyipokey and companybasekeymaskey tables. The output master table will have each company name and the dates and amounts if they received a ipo or ma. As discussed in [[#Joining companybasekeys with maskeys and ipokeys|Joining companybasekeys with maskeys and ipokeys]] the master table includes the exit deal which had the minimum date so duplicate rows should not crop up in the master table.
--44740
\COPY companybaseipomasmaster TO 'companybaseipomasmaster.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
You can run checks on the ipo and mas counts to make sure everything joined properly. Any duplicate keys that were not cleaned up in previous steps will make this master table a complete mess due to all the joins so make sure you've followed the process fully. Below are some of the checks I ran:
SELECT COUNT(*) FROM companybaseipomasmaster WHERE masannounceddate IS NOT NULL;
--8610
SELECT COUNT(*) FROM companybaseipomasmaster WHERE mastransactionamtk IS NOT NULL;
--8610
SELECT COUNT(*) FROM companybaseipomasmaster WHERE ipoissuedate IS NOT NULL;
--2312
SELECT COUNT(*) FROM companybaseipomasmaster WHERE ipoprincipalamtk IS NOT NULL;
--2312
Everything looks good. These counts are compared against the key tables and core tables built in the previous steps.
==Name Based Matching companybase keys to ipo keys==
