220
edits
Changes
Jump to navigation
Jump to search
→Name Based Matching companybase keys to mas keys
==Name Based Matching companybase keys to mas keys==
Before attempting to match companybasecore with mascore you need a clean table or you will get many errors in the matcher output file. Luckily the core files should already contain distinct keys if you've followed the process. However running the matcher will still yield many errors. So we will filter the mas keys some more. The first thing is to remove mas keys (targetname, announceddate, targetstatecode) where the announceddate falls within the same week. Keep the key that has the minimum announceddate and discard the higher date. Shown below:
DROP TABLE maskeys;
CREATE TABLE maskeys AS
SELECT DISTINCT targetname, targetstatecode, announceddate
FROM mascore;
--114825
DROP TABLE maskeysmindates;
CREATE TABLE maskeysmindates AS
SELECT targetname, targetstatecode, MIN(announceddate) AS announceddate
FROM mascore
GROUP BY targetname, targetstatecode;
--113236
DROP TABLE maskeysdatewindow;
CREATE TABLE maskeysdatewindow AS
SELECT maskeys.*, maskeysmindates.announceddate as minanndate,
CASE WHEN maskeys.announceddate - INTERVAL '7 day' > maskeysmindates.announceddate THEN 1::int
ELSE 0::int
END AS dateflag
FROM maskeys JOIN maskeysmindates ON (maskeys.targetname = maskeysmindates.targetname AND
maskeys.targetstatecode = maskeysmindates.targetstatecode);
--114825
CREATE TABLE maskeysdatefiltered AS
SELECT targetname, targetstatecode, announceddate
FROM maskeysdatewindow
WHERE dateflag = 0;
--113267
==Creating Stage Flags Table==
