FROM portcoipojoin LEFT JOIN iponoduplicates AS ipo ON portcoipojoin.file2issuer = ipo.issuer AND
portcoipojoin.file2issuedate = ipo.issuedate AND portcoipojoin.statecode = ipo.statecode;
DROP TABLE portcoipojoinmajoin;
--44774
\COPY portcoipojoinmajoin TO 'portcoipojoinmajoin.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
For mas you will need to join to the masnodupes table because you will want acquiror name and transaction amount. The matcher file only contains the key without the extra information.
DROP TABLE portcomajoinmasnodupes;
CREATE TABLE portcomajoinmasnodupes AS
SELECT portcomajoin.*, masnodupes.*
FROM portcomajoin LEFT JOIN masnodupes ON masnodupes.targetname = portcomajoin.file2targetname AND
masnodupes.targetstatecode = portcomajoin.statecode AND masnodupes.announceddate = portcomajoin.file2announceddate;
--44755
Then join with the portco table again and extract out the city and address.
--44878
\COPY portcoipomalocation TO 'portcoipomalocation.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--now join the two ma and ipo tables together on coname, statecode, datefirstinv
FROM portcomajoinmasnodupes AS ma JOIN portcoipojoinamt AS ipo ON ma.coname = ipo.coname AND ma.statecode = ipo.statecode AND ma.datefirstinv = ipo.datefirstinv;
--44755
\COPY portcoipoma TO 'portcoipoma.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
DROP TABLE portcoipomanodupes;
CREATE TABLE portcoipomanodupes AS
SELECT *
FROM portcoipoma WHERE transactionamt IS NULL OR proceedsamt IS NULL;
--44706
\COPY portcoipomanodupes TO 'portcoipomanodupes.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
==Joining geo with portcoipomas==
FROM portcoipomalocationexclude as portco LEFT JOIN geo ON (portco.coname = geo.coname AND portco.city = geo.city AND
portco.datefirstyear = geo.startyear);
You will notice that the output has blank entries for the noaddress flag. This indicates that the tables aren't joining correctly. Indeed you will see that the company Capella Systems, Inc. in the geo table is named Capella Systems Inc. in the portcoipomalocationexclude table. Therefore you will need to use the Matcher tool to match the keys in the two tables.
CREATE TABLE geomatcher (
geoconame varchar(255),
geocity varchar(100),
geostartyear varchar(4),
file2coname varchar(255),
file2city varchar(100),
file2datefirstyear varchar(4)
);
\COPY geomatcher FROM 'matcheroutputgeo.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--43770
Geolookupbatch1 contains the first 2299 records. Geolookupbatch2 contains 6597 records. Geolookupbatch3 contains 2527 records. This sums to 11423 which matches the number of initial lookups?