Business Scenario

Escenario de negocio

An organization receives a payroll, budget, billing, or operational CSV file. Most numeric values are reasonable, but some records contain values that are too high, too low, missing, or incorrectly entered. These outliers can distort totals, averages, forecasts, budgets, dashboards, and downstream calculations.

Una organización recibe un archivo CSV de nómina, presupuesto, facturación u operaciones. La mayoría de los valores numéricos son razonables, pero algunos registros contienen valores demasiado altos, demasiado bajos, vacíos o mal ingresados. Estos outliers pueden distorsionar totales, promedios, pronósticos, presupuestos, dashboards y cálculos posteriores.

What You Will Learn

Qué aprenderás

• Import a CSV file containing numeric fields.
• Define acceptable minimum and maximum thresholds.
• Detect records outside the expected range.
• Separate normal records from records that require review.
• Create clean outputs for validation, auditing, payroll review, and reporting.

• Importar un archivo CSV con campos numéricos.
• Definir límites mínimos y máximos aceptables.
• Detectar registros fuera del rango esperado.
• Separar registros normales de registros que requieren revisión.
• Crear salidas limpias para validación, auditoría, revisión de nómina y reportes.

Sample Input

Entrada de ejemplo

Base Example

Ejemplo base

The basic idea is to return only records where the salary is outside the logical range.

La idea base es devolver solamente los registros donde el salario está fuera del rango lógico.

Import-Csv "C:\Data\Nominas.csv" |
Where-Object { [int]$_.Salario -gt 150000 -or [int]$_.Salario -lt 20000 }

Step 1 — Define the Source File

Paso 1 — Definir el archivo fuente

$sourceFile = "C:\Data\Nominas.csv"
$cleanFile = "C:\Data\Nominas_Validas.csv"
$reviewFile = "C:\Data\Nominas_Outliers.csv"

Step 2 — Import the Data

Paso 2 — Importar los datos

$records = Import-Csv -Path $sourceFile

Step 3 — Define Logical Thresholds

Paso 3 — Definir rangos lógicos

For this exercise, salaries below 20,000 or above 150,000 will be flagged for review.

Para este ejercicio, los salarios menores de 20,000 o mayores de 150,000 serán marcados para revisión.

$minimumSalary = 20000
$maximumSalary = 150000

Step 4 — Detect Outliers

Paso 4 — Detectar outliers

$processed = foreach ($record in $records) {
    $salaryText = $record.Salary
    $salary = 0

    if (-not [int]::TryParse($salaryText, [ref]$salary)) {
        $status = "Review"
        $issue = "Salary is not numeric"
    }
    elseif ($salary -lt $minimumSalary) {
        $status = "Review"
        $issue = "Salary below minimum threshold"
    }
    elseif ($salary -gt $maximumSalary) {
        $status = "Review"
        $issue = "Salary above maximum threshold"
    }
    else {
        $status = "Valid"
        $issue = ""
    }

    $record | Add-Member -NotePropertyName ValidationStatus -NotePropertyValue $status -Force
    $record | Add-Member -NotePropertyName ValidationIssue -NotePropertyValue $issue -Force
    $record
}

Step 5 — Export Valid and Review Records

Paso 5 — Exportar registros válidos y para revisión

$validRecords = $processed | Where-Object { $_.ValidationStatus -eq "Valid" }
$reviewRecords = $processed | Where-Object { $_.ValidationStatus -eq "Review" }

$validRecords | Export-Csv -Path $cleanFile -NoTypeInformation
$reviewRecords | Export-Csv -Path $reviewFile -NoTypeInformation

Complete Script

Script completo

# PowerShell Advanced Topic #9
# Detect numeric anomalies / outliers

$sourceFile = "C:\Data\Nominas.csv"
$cleanFile = "C:\Data\Nominas_Validas.csv"
$reviewFile = "C:\Data\Nominas_Outliers.csv"

$minimumSalary = 20000
$maximumSalary = 150000

$records = Import-Csv -Path $sourceFile

$processed = foreach ($record in $records) {
    $salaryText = $record.Salary
    $salary = 0

    if (-not [int]::TryParse($salaryText, [ref]$salary)) {
        $status = "Review"
        $issue = "Salary is not numeric"
    }
    elseif ($salary -lt $minimumSalary) {
        $status = "Review"
        $issue = "Salary below minimum threshold"
    }
    elseif ($salary -gt $maximumSalary) {
        $status = "Review"
        $issue = "Salary above maximum threshold"
    }
    else {
        $status = "Valid"
        $issue = ""
    }

    $record | Add-Member -NotePropertyName ValidationStatus -NotePropertyValue $status -Force
    $record | Add-Member -NotePropertyName ValidationIssue -NotePropertyValue $issue -Force
    $record
}

$validRecords = $processed | Where-Object { $_.ValidationStatus -eq "Valid" }
$reviewRecords = $processed | Where-Object { $_.ValidationStatus -eq "Review" }

$validRecords | Export-Csv -Path $cleanFile -NoTypeInformation
$reviewRecords | Export-Csv -Path $reviewFile -NoTypeInformation

Write-Host "Numeric anomaly detection completed."
Write-Host "Valid records:" $validRecords.Count
Write-Host "Records requiring review:" $reviewRecords.Count

Expected Output

Resultado esperado

Real-World Applications

Aplicaciones reales

Recommendations

Recomendaciones

• Define thresholds with business owners before flagging records.
• Do not delete outliers automatically; export them for review.
• Keep validation rules documented for audit purposes.
• Use separate thresholds for different roles, departments, or pay types when needed.
• Review outliers before importing data into payroll, financial, or reporting systems.

• Define los rangos con los responsables del proceso antes de marcar registros.
• No elimines outliers automáticamente; expórtalos para revisión.
• Mantén documentadas las reglas de validación para auditoría.
• Usa rangos distintos por cargo, departamento o tipo de pago cuando sea necesario.
• Revisa los outliers antes de importar datos a nómina, finanzas o sistemas de reporte.