From ce63570c095de0a8ec212524589b3cb511b28aa9 Mon Sep 17 00:00:00 2001 From: Stevan Earl Date: Wed, 19 Aug 2026 16:30:55 -0700 Subject: [PATCH] Split Problems #16 and #18 cleanup from loading --- conversion/clean.sql | 12 ++++++++++++ conversion/load_community_membership_update_log.sql | 8 +++----- 2 files changed, 15 insertions(+), 5 deletions(-) diff --git a/conversion/clean.sql b/conversion/clean.sql index d79f57e..59c7e2f 100644 --- a/conversion/clean.sql +++ b/conversion/clean.sql @@ -199,6 +199,18 @@ UPDATE biography WHERE b_momid IS DISTINCT FROM NULLIF(BTRIM(b_momid), ''); +-- +-- COMMUNITY_MEMBERSHIP_UPDATE_LOG +-- + +-- Problems #16 and #18 +-- Preserve the source value while removing surrounding whitespace. Empty +-- values are mapped to the unknown person when production data are loaded. +UPDATE community_membership_update_log + SET made_by = BTRIM(made_by) + WHERE made_by IS DISTINCT FROM BTRIM(made_by); + + -- -- follow -- diff --git a/conversion/load_community_membership_update_log.sql b/conversion/load_community_membership_update_log.sql index a1ad076..a3c8690 100644 --- a/conversion/load_community_membership_update_log.sql +++ b/conversion/load_community_membership_update_log.sql @@ -33,11 +33,9 @@ INSERT INTO comm_memb_log ( , chimp_id , update_description , update_rationale - , CASE -- This solves NULL data problems; problems #18, #16 - WHEN made_by IS NULL - THEN 'UNK' - ELSE made_by - END CASE + -- Map missing values to the unknown person; problems #16 and #18. + -- Surrounding whitespace was removed when the clean schema was built. + , COALESCE(NULLIF(log.made_by, ''), 'UNK') FROM clean.community_membership_update_log AS log -- Get a consistent id generation ORDER BY date_of_update, chimp_id, update_rationale, made_by; -- 2.34.1