MEDSHIELD KNIME REBUILD BLUEPRINT — REQUIRED EXECUTABLE PIPELINE
1 SOURCE CONTROL: 2017-2025 data
2 INGEST: List Files/Folders → Table Row to Variable Loop Start → CSV Reader bound to path variable. Detect header row by year/schema; do not hard-code skip=3 because 2017/2018/2019 use row 5 headers while 2020–2025 use row 6.
3 STANDARDIZE: Column Rename by schema-year → String Cleaner → Missing Value/Rule Engine → String to Date&Time → String to Number. Canonical columns: area, dr_number, delivery_date, product, qty, unit_price, gross_or_total_cp, discount, net_revenue, transfer_price, total_tp, net_income.
4 QUALITY GATE: reject invalid/unparseable date, missing product/area, qty≤0, net_revenue≤0; retain QA reject reason. Flag but do not silently delete financial anomalies (negative margin, margin>revenue). Do not zero-impute missing demand.
5 LEGACY/BULK: isolate #REF!, blank products, and contract-name aggregate rows (e.g., PAGBILAO # amount). Backward approximation/disaggregation must be auditable and separate from clean observed rows.
6 MASTER MAPPING: area/territory + customer/account type rule-based encoding; product alias → canonical SKU; medicine/non-medicine; mapping_status and review queue. Require 100% mapped analytical rows or quarantine unmatched records.
7 FACT GRAIN: preserve transaction fact first; derive monthly product×territory fact. External DOH/PAGASA signals align by shared date/month and area keys before modeling.
8 DESCRIPTIVE: behavior grouping; STL seasonality; 80/20 product/territory/account ranking; YoY growth; territory revenue/net income; institutional-client concentration.
9 PREDICTIVE: time-aware train/validation/test; seasonal-naive benchmark; Prophet baseline; Prophet+DII; Prophet+RSI; MAE/RMSE/MAPE + residual diagnostics; XGBoost urgency and ABC with held-out labels and class metrics. No random leakage across time.
10 PRESCRIPTIVE: forecast → EOQ → safety stock → ROP; outbreak μ+2σ alerts; typhoon scenario; MCDA → constrained LP allocation; collaborative filtering; dead-stock/stop-purchase flags. Validate assumptions and feasibility before recommendations.
11 LOAD: write dimension/fact outputs to MedShield Postgres schemas using surrogate keys, pipeline_run_key/source lineage, snapshot dates, and mapping status. Publish dashboard-ready tables only after QA pass.
12 QA: row-count reconciliation per year, duplicate tests, referential integrity, mapping coverage, aggregation reconciliation, forecast backtest, LP feasibility, and output acceptance tests.
