220
edits
Changes
Jump to navigation
Jump to search
→Import Flagged Matcher Data Into PSQL
\COPY ipomatcheroutputwithexcludeflag FROM 'iposmatcheroutput-nohead.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--2591
==Joining portco with ipo and mas==
In order to match the company with the ipo or acquisition date we need to join the three tables together. Do this in a two step process. First join with ipo and then take the resulting table and join with mas. This will give you companies with an ipo column and acquisition column. There will be a lot of blanks because some companies didn't have ipos or acquisitions.
DROP TABLE portcoipojoin;
CREATE TABLE portcoipojoin AS
SELECT coname, statecode, datefirstinv, matcher.file2issuer, matcher.file2issuedate
FROM companybase LEFT JOIN ipomatcheroutputwithexcludeflag AS matcher ON companybase.coname = matcher.file1coname AND
companybase.statecode = matcher.file1statecode AND companybase.datefirstinv = matcher.file1datefirstinv AND
matcher.excludefinal = FALSE;
--44773
\COPY portcoipojoin TO 'portcoipojoin.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
DROP TABLE portcoipojoinmajoin;
CREATE TABLE portcoipojoinmajoin AS
SELECT portco.*, matcher.file2targetname, matcher.file2announceddate
FROM portcoipojoin AS portco LEFT JOIN masmatcheroutputwithexcludeflag AS matcher ON portco.coname = matcher.file1coname
AND portco.statecode = matcher.file1statecode AND portco.datefirstinv = matcher.file1datefirstinv AND matcher.excludefinal
= FALSE;
--44774
\COPY portcoipojoinmajoin TO 'portcoipojoinmajoin.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
Then join with the portco table again and extract out the city and address.
DROP TABLE portcoipomalocation;
CREATE TABLE portcoipomalocation AS
SELECT portcojoin.*, city, addr1, addr2, indclass, indsubgroup3, indminor
FROM portcoipojoinmajoin AS portcojoin LEFT JOIN companybase ON portcojoin.coname = companybase.coname AND
portcojoin.statecode = companybase.statecode;
--44878
\COPY portcoipomalocation TO 'portcoipomalocation.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
