---
type: inbox
area: inbox
status: inbox
date: 2026-05-03
created: 2026-05-03
updated: 1980-01-01
tags:
  - inbox
---
Pour quoi cette colonne n'existe pas DT_FACTORY_ORDER_ENTRY_DATE

```
concat(
  'DLV-',
  formatDateTime(utcNow(), 'yyMMddHHmmss'),
  '-',
  substring(guid(), 0, 8)
)
```
#### Answer to my question!!!
### **Analysis & Recommendations for Your Date-Based Feature Engineering**

Your current approach is **solid**, but here are key improvements and additional features to enhance robustness:

---

#### **1. Refined Feature Engineering**

| **Feature**                           | **Formula**                                                                                                                                                                     | **Purpose**                                               |
| ------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------- |
| **Order-to-Production Days**          | `DT_VEHICLE_FACTORY_PRODUCTION_DATE - DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE`                                                                                                     | Captures order fulfillment efficiency.                    |
| **Production-to-Shipping-Ready Days** | `DT_READY_TO_SHIP_FROM_LOGISTIC_PLANT_COMPOUND_DATE - DT_VEHICLE_FACTORY_PRODUCTION_DATE`                                                                                       | Measures post-production preparation delays.              |
| **Shipping-Ready-to-Shipping Days**   | `DT_SHIPPING_ORDER_TO_NSC_COMPOUND_CREATION_DATE - DT_READY_TO_SHIP_FROM_LOGISTIC_PLANT_COMPOUND_DATE`                                                                          | Highlights delays in initiating shipment after readiness. |
| **Shipping Transit Days**             | `DT_ARRIVAL_AT_DESTINATION_BY_TRANSPORTER_DATE - DT_SHIPPING_ORDER_TO_NSC_COMPOUND_CREATION_DATE`                                                                               | Quantifies logistics performance.                         |
| **Customs Clearance Days**            | `DT_CUSTOMS_OFFICE_OUTBOUND_DATE - DT_CUSTOMS_OFFICE_INBOUND_DATE`                                                                                                              | Tracks customs delays (only for international shipments). |
| **Total Lead Time vs Expected**       | `(DT_ARRIVAL_AT_DESTINATION_BY_TRANSPORTER_DATE - DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE) - (DT_EXPECTED_DELIVERY_TO_FINAL_CUSTOMER_DATE - DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE)` | Compares actual vs expected total timeline.               |

---

#### **2. Missing Data Handling**
- **Boolean Flag for Imputed Dates**:  
  ```python
  filtered_df["MISSING_SHIPPING_ORDER"] = filtered_df["DT_SHIPPING_ORDER_TO_NSC_COMPOUND_CREATION_DATE"].isna().astype(int)
  filtered_df["MISSING_CUSTOMS_INBOUND"] = filtered_df["DT_CUSTOMS_OFFICE_INBOUND_DATE"].isna().astype(int)
  ```
- **Impute Critical Dates**:  
  Use median time lags for missing shipping/customs dates (e.g., impute `DT_SHIPPING_ORDER_TO_NSC_COMPOUND_CREATION_DATE` with `DT_READY_TO_SHIP_FROM_LOGISTIC_PLANT_COMPOUND_DATE + 2 days`).

---

#### **3. Additional Features**
| **Feature**                          | **Formula**                                                                 | **Purpose**                                                                 |
|--------------------------------------|-----------------------------------------------------------------------------|-----------------------------------------------------------------------------|
| **Is International Shipment**        | `1` if `DT_CUSTOMS_OFFICE_INBOUND_DATE` is not null, else `0`               | Flags international shipments for segmentation.                             |
| **Day of Week for Order Entry**      | `DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE.dt.dayofweek`                         | Checks if orders placed on weekends have longer lead times.                 |
| **Month of Production**              | `DT_VEHICLE_FACTORY_PRODUCTION_DATE.dt.month`                               | Identifies seasonal production bottlenecks (e.g., holiday shutdowns).       |

---

#### **4. Validation Checks**
- **Consistency Checks**:  
  Ensure `DT_VEHICLE_FACTORY_PRODUCTION_DATE` ≥ `DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE`.  
  Flag rows where `DT_ARRIVAL_AT_DESTINATION_BY_TRANSPORTER_DATE` < `DT_SHIPPING_ORDER_TO_NSC_COMPOUND_CREATION_DATE` (invalid dates).

---

### **Final Feature List**
```python
columns_to_keep = [
    "DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE",
    "DT_VEHICLE_FACTORY_PRODUCTION_DATE",
    "DT_VEHICLE_PASSED_TO_SALES_DATE",
    "DT_READY_TO_SHIP_FROM_LOGISTIC_PLANT_COMPOUND_DATE",
    "DT_SHIPPING_ORDER_TO_NSC_COMPOUND_CREATION_DATE",
    "DT_ARRIVAL_AT_DESTINATION_BY_TRANSPORTER_DATE",
    "DT_CUSTOMS_OFFICE_INBOUND_DATE",
    "DT_CUSTOMS_OFFICE_OUTBOUND_DATE",
    "DT_EXPECTED_DELIVERY_TO_FINAL_CUSTOMER_DATE"
]

engineered_features = [
    "ORDER_TO_PRODUCTION_DAYS",
    "PRODUCTION_TO_SHIPPING_READY_DAYS",
    "SHIPPING_READY_TO_SHIPPING_DAYS",
    "SHIPPING_TRANSIT_DAYS",
    "CUSTOMS_CLEARANCE_DAYS",
    "TOTAL_LEAD_VS_EXPECTED_DAYS",
    "IS_INTERNATIONAL",
    "MISSING_SHIPPING_ORDER",
    "MISSING_CUSTOMS_INBOUND",
    "ORDER_DAY_OF_WEEK",
    "PRODUCTION_MONTH"
]
```

---

### **Summary**
- **Strengthened Features**: Added stage-specific time deltas and validation checks.  
- **Handled Missing Data**: Flags and imputation ensure robustness.  
- **Temporal Context**: Day/week/month features capture cyclical patterns.  

This approach ensures you diagnose delays at **every stage** while maintaining data integrity.


### **Solution: Handling Missing Values in Date Columns**

Here’s a structured approach to address missing values in your selected date columns, aligned with automotive supply chain logic:

---

#### **1. Columns with Minimal Missing Values (<1% of 10K Rows)**

|**Column**|**Missing**|**Handling Strategy**|
|---|---|---|
|`DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE`|52|**Drop rows**: Critical start date; missing values disrupt the entire timeline.|
|`DT_VEHICLE_PASSED_TO_SALES_DATE`|52|**Drop rows**: Small impact; ensures accuracy in post-production pipeline analysis.|

---

#### **2. Columns with Moderate Missing Values (3–50%)**

| **Column**                                           | **Missing** | **Handling Strategy**                                                                                                                                            |
| ---------------------------------------------------- | ----------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `DT_READY_TO_SHIP_FROM_LOGISTIC_PLANT_COMPOUND_DATE` | 307         | **Impute**: Use `DT_VEHICLE_FACTORY_PRODUCTION_DATE + median lag` (e.g., 3 days).                                                                                |
| `DT_SHIPPING_ORDER_TO_NSC_COMPOUND_CREATION_DATE`    | 5257        | **Impute + Flag**: Calculate as `DT_READY_TO_SHIP_FROM_LOGISTIC_PLANT_COMPOUND_DATE + median lag` (e.g., 2 days). Add `MISSING_SHIPPING_ORDER_FLAG` (1=imputed). |
|                                                      |             |                                                                                                                                                                  |

---

#### **3. Columns with High Missing Values (>50%)**

| **Column**                           | **Missing** | **Handling Strategy**                                                                                                                       |
| ------------------------------------ | ----------- | ------------------------------------------------------------------------------------------------------------------------------------------- |
| `DT_VEHICLE_FACTORY_PRODUCTION_DATE` | 7877        | **Impute + Flag**: Use `DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE + median production time` (e.g., 14 days). Add `MISSING_PRODUCTION_DATE_FLAG`. |
| `DT_CUSTOMS_OFFICE_INBOUND_DATE`     | 7593        | **Drop column**: Irrelevant for domestic shipments (most orders).                                                                           |
| `DT_CUSTOMS_OFFICE_OUTBOUND_DATE`    | 9190        | **Drop column**: Same as above.                                                                                                             |

---

#### **4. Critical Non-Missing Columns**

|**Column**|**Role**|
|---|---|
|`DT_ARRIVAL_AT_DESTINATION_BY_TRANSPORTER_DATE`|Target variable (actual delivery date).|
|`DT_EXPECTED_DELIVERY_TO_FINAL_CUSTOMER_DATE`|Target variable (expected delivery date).|

---

#### **5. Feature Engineering**

Derive these features after handling missing values:

|**Feature**|**Formula**|**Purpose**|
|---|---|---|
|`ORDER_TO_PRODUCTION_DAYS`|`DT_VEHICLE_FACTORY_PRODUCTION_DATE - DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE`|Quantifies production lead time.|
|`PRODUCTION_TO_SHIPPING_READY_DAYS`|`DT_READY_TO_SHIP_FROM_LOGISTIC_PLANT_COMPOUND_DATE - DT_VEHICLE_FACTORY_PRODUCTION_DATE`|Tracks post-production delays.|
|`SHIPPING_TRANSIT_DAYS`|`DT_ARRIVAL_AT_DESTINATION_BY_TRANSPORTER_DATE - DT_SHIPPING_ORDER_TO_NSC_COMPOUND_CREATION_DATE`|Measures logistics efficiency.|
|`IS_INTERNATIONAL_SHIPMENT`|`1` if `DT_CUSTOMS_OFFICE_INBOUND_DATE` is not null, else `0`|Segments domestic vs. international shipments (if customs columns retained).|

---

#### **6. Validation & Business Logic**

- **Consistency Checks**:
    
    - Ensure `DT_VEHICLE_FACTORY_PRODUCTION_DATE` ≥ `DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE`.
        
    - Flag invalid timelines (e.g., shipping dates before production dates).
        
- **Domain Logic**:
    
    - If `DT_VEHICLE_FACTORY_PRODUCTION_DATE` is missing, assume delays in manufacturing.
        
    - Use `MISSING_SHIPPING_ORDER_FLAG` to identify systemic logistics tracking issues.
        

---

### **Summary**

- **Minimal Missingness**: Drop rows for critical start dates (`DT_COMMERCIAL_ORDER_FIRST_ENTRY_DATE`).
    
- **Moderate Missingness**: Impute using domain logic (e.g., median lags) and flag imputed values.
    
- **High Missingness**: Drop irrelevant columns (customs dates) and impute production dates.
    
- **Feature Engineering**: Focus on time deltas between key stages and segment international shipments.
    

This approach balances data retention with accuracy, ensuring your model captures delays across the supply chain.