Select Fields From Duplicate Records for an Array

Question

I've got a CSV file that is imported that looks like this:

Customer ID  Contract Start  Contract End   Region  Customer
2-213456     2/20/2018       1/1/2030       NA      Acme
2-213456     6/18/2019       6/17/2020      NA      Acme
2-213456     6/18/2020       6/30/2021      NA      Acme
3-213458     6/27/2019       6/26/2020      CAN     Acme Shipping
2-123456     6/27/2020       6/27/2021      AUS     Acme Manufacturing
5-123576     6/29/2019       6/28/2020      AUS     Acme Storage

Which I'm trying to build an array that only has the unique values (Customer ID) but, would like to include the earliest Contract Start date and the latest Contract End date to get a result like:

Customer ID   Contract Start    Contract End    Region  Customer
2-213456      2/20/2018         6/30/2021       NA      Acme
3-213458      6/27/2019         6/26/2020       CAN     Acme Shipping
2-123456      6/27/2020         6/27/2021       AUS     Acme Manufacturing
5-123576      6/29/2019         6/28/2020       AUS     Acme Storage

This is what I have but, I keep getting a System.Object[] for the dates

$Data = import-csv -path "C:\Customers.csv"

$Final = @()
$N = 0
$count = $Data.count

foreach ($record in $Data)
{
    Write-Host "Record " $N " of " $Count

    $Rec = New-Object System.Object
    $Rec | Add-Member -type NoteProperty -name "Customer ID" -value $record.'Customer ID'
    $StartDate = $Data | Foreach-Object {$_.'Contract Start' = [DateTime]$_.'Contract Start'; $_} | Group-Object 'Customer ID' | Foreach-Object {$_.Group | Sort-Object 'Contract Start' | Select-Object -Property $record.'Contract Start' -first 1}
    $Rec | Add-Member -type NoteProperty -name "Contract Start" -value $StartDate
    $EndDate = $Data | Foreach-Object {$_.'Contract End' = [DateTime]$_.'Contract End'; $_} | Group-Object 'Customer ID' | Foreach-Object {$_.Group | Sort-Object 'Contract End' | Select-Object -Property $record.'Contract End' -Last 1}
    $Rec | Add-Member -type NoteProperty -name "Contract End" -value $EndDate
    $Rec | Add-Member -type NoteProperty -name "Region" -value $record.'Region'
    $Rec | Add-Member -type NoteProperty -name "Customer" -value $record.'Customer'

    $Final += $Rec
    $N++
}

Ash · Accepted Answer · 2020-05-12 00:13:41Z

I got a lot of errors about Datetime trying to replicate what you have posted above. You've tried to do a lot in one place when setting and sorting the start and end dates, so our first task is to simplify that. Knowing that you could potentially have a lot of customer data, I thought it best to group the customers by their ID in a hashtable. That way we can call the customer ID and immediately just have their records. PowerShell classes allow us to create a couple of methods to import the data in to the hashtable, parse the dates as part of the import. The final method exports your data picking the earliest start date, and the latest end date. Fully tested solution below.

class Customers {
    [hashtable]$Accounts

    # Constructor
    Customers() {
        $this.Accounts = @{}
    }

    # Methods
    [void]AddCustomerData([psobject[]]$Records) {
        foreach ($Record in $Records) {
            # Convert the dates to datetime objects so we can sort them later
            $Record = $this.ParseDates($Record)
            $ID = $Record."Customer ID"
            # If the hashtable already contains the customer ID, we need to add the new record to their existing ones.
            if ($this.Accounts.Contains($ID)) {
                $CustomerRecords = $this.Accounts[$ID]
                $CustomerRecords += $Record
                $this.Accounts[$ID] = $CustomerRecords
            }
            # If it doesn't we create a new record with the value as an array.
            else {
                $this.Accounts[$ID] = @(,$Record)
            }
        }
    }

    [psobject]ParseDates([psobject]$Row) {
        # Your dates appear to be US format, so I've kept them that way, change the culture from 'en-US' if you need to.
        $Row."Contract Start" = [Datetime]::Parse($Row."Contract Start",[cultureinfo]::new("en-US",$false))
        $Row."Contract End" = [Datetime]::Parse($Row."Contract End",[cultureinfo]::new("en-US",$false))
        return $Row
    }

    [psobject[]]PrintCustomerData() {
        $CustomerData = @()
        # Loop through the hashtable
        $this.Accounts.GetEnumerator() | ForEach-Object {
            $Contracts = $_.Value
            # Find the earliest start date for the current customer by sorting in ascending order
            $StartDate = $Contracts."Contract Start" | Sort-Object | Select-Object -First 1
            # Find the latest end date for the current customer by sorting in descending order
            $EndDate = $Contracts."Contract End" | Sort-Object -Descending | Select-Object -First 1
            # Create a new PSObject for each customer, selecting a Unique value for Region and Customer as it should be the same across records
            $CustomerData += [PSCustomObject] @{
                "Customer ID" = $_.Key
                "Contract Start" = $StartDate
                "Contract End" = $EndDate
                Region = $($Contracts | Select-Object -Unique -ExpandProperty Region)
                Customer = $($Contracts | Select-Object -Unique -ExpandProperty Customer)
            }
        }
        return $CustomerData
    }
}

Usage:

$csv = Import-Csv -Path .\Desktop\test.csv
# Create a new instance of the class
$customers = [Customers]::new()
# Add the CSV data to a the Accounts hashtable
$customers.AddCustomerData($csv)
# Print out the data from the hashtable in the desired format.
$customers.PrintCustomerData() | Format-Table -AutoSize

Customer ID Contract Start      Contract End        Region Customer
----------- --------------      ------------        ------ --------
2-213456    20/02/2018 00:00:00 01/01/2030 00:00:00 NA     Acme
2-123456    27/06/2020 00:00:00 27/06/2021 00:00:00 AUS    Acme Manufacturing
3-213458    27/06/2019 00:00:00 26/06/2020 00:00:00 CAN    Acme Shipping
5-123576    29/06/2019 00:00:00 28/06/2020 00:00:00 AUS    Acme Storage

And now you have your records in a hashtable, you can do other awesome stuff like look up the records for a particular customer.

$customers.Accounts['2-213456'] | Format-Table -AutoSize

Customer ID Contract Start      Contract End        Region Customer
----------- --------------      ------------        ------ --------
2-213456    20/02/2018 00:00:00 01/01/2030 00:00:00 NA     Acme
2-213456    18/06/2019 00:00:00 17/06/2020 00:00:00 NA     Acme
2-213456    18/06/2020 00:00:00 30/06/2021 00:00:00 NA     Acme

RoadRunner · Accepted Answer · 2020-05-12 02:49:09Z

Using this data.csv as an example input:

Customer ID,Contract Start,Contract End,Region,Customer
2-213456,2/20/2018,1/1/2030,NA,Acme
2-213456,6/18/2019,6/17/2020,NA,Acme
2-213456,6/18/2020,6/30/2021,NA,Acme
3-213458,6/27/2019,6/26/2020,CAN,Acme Shipping
2-123456,6/27/2020,6/27/2021,AUS,Acme Manufacturing
5-123576,6/29/2019,6/28/2020,AUS,Acme Storage

We can use Group-Object to group by Customer ID and use Sort-Object to sort by datetime versions of Contract Start and Contract End. Then we can construct a new System.Management.Automation.PSCustomObject for each compressed record, and format the System.Object[] array with Format-Table.

$array = Import-Csv -Path .\data.csv | Group-Object -Property "Customer ID" | ForEach-Object {
    $contractStart = $_.Group |
        Sort-Object -Property @{Expression = {[datetime]$_."Contract Start"}} |
            Select-Object -First 1

    $contractEnd = $_.Group |
        Sort-Object -Property @{Expression = {[datetime]$_."Contract End"}} |
            Select-Object -Last 1

    [PSCustomObject]@{
        "Customer ID" = $_.Name
        "Contract Start" = $contractStart."Contract Start"
        "Contract End" = $contractEnd."Contract End"
        "Region" = $contractStart.Region
        "Customer" = $contractStart.Customer
    }
}

$array.GetType().FullName

$array | Format-Table -AutoSize

Which results in the following table result:

System.Object[]

Customer ID Contract Start Contract End Region Customer
----------- -------------- ------------ ------ --------
2-123456    6/27/2020      6/27/2021    AUS    Acme Manufacturing
2-213456    2/20/2018      1/1/2030     NA     Acme
3-213458    6/27/2019      6/26/2020    CAN    Acme Shipping
5-123576    6/29/2019      6/28/2020    AUS    Acme Storage

Collectives™ on Stack Overflow

Select Fields From Duplicate Records for an Array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related