bo1
May 27, 2016, 11:07pm
1
Hello Powershell.org . I have been searching google all day for an answer to this and I am just not getting what I need so hopefully somebody here can offer some assistance. The short explanation is that I have 2 arrays that each contain some duplicate info. See below
$ObjColl
Name TotalUsers TotalInbox
IT 3 3
Accounting 4 2
Marketing 2 1
And the second one…
$ObjColl1
Name TotalUsers TotalInbox
Accounting 2 1
Marketing 2 1
So when I add them together:
$Final += $Objcoll
$Final += $Objcoll1
I get the following output:
$Final
Name TotalUsers TotalInbox
IT 3 3
Accounting 4 2
Marketing 2 1
Accounting 2 1
Marketing 2 1
What I actually want to do is aggregate the totals rather than doing a literal “combining” of the arrays. How can this be accomplished? Example of desired output below:
$Final
Name TotalUsers TotalInbox
IT 3 3
Accounting 6 3
Marketing 4 2
Thanks in advance for any assistance!
You would have to compare the actual values in the array. It may or may not be the most efficient way, but you could use a nested foreach loop such as:
Foreach ($name in $ObjColl)
{
Foreach ($name1 in $ObjColl1)
{
If ($name.Name -eq $name1.Name)
{
$row = new-object PSObject -Property @{
Name = $name.Name;
TotalUsers = ($name.TotalUsers + $name1.TotalUsers);
TotalInbox = ($name.TotalInbox + $name1.TotalInbox)
}
$Final += $row
}
}
}
This is assuming that the TotalUsers and TotalInbox columns are formatted as integers and not strings.
$Final
Name TotalUsers TotalInbox
Accounting 6 3
Marketing 4 2
You don’t need the first two custom objects as you already have them but I needed to build them for example.
$one = 'IT','Accounting','Marketing' | % {
[pscustomobject]@{
name = $_
totalusers = (1..9) |Get-Random
totalinbox = (1..9) |Get-Random
}
}
$two = 'IT','Accounting','Marketing' | % {
[pscustomobject]@{
name = $_
totalusers = (1..9) |Get-Random
totalinbox = (1..9) |Get-Random
}
}
$total = $one + $two
$total | group name | %{
$n = $_.name
$group = $total | ? {$_.name -eq $n}
[pscustomobject]@{
name = $n
totalusers = ($group.totalusers | Measure-Object -Sum).sum
totalinbox = ($group.totalinbox | Measure-Object -Sum).sum
}
}
1 Like
Sorry I misunderstood the question, I thought you wanted ONLY duplicates, do what Dan said
$ObjCol = "
Name TotalUsers TotalInbox
IT 3 3
Accounting 4 2
Marketing 2 1" -split "`n"
$ObjCol2 = "
Name TotalUsers TotalInbox
Accounting 2 1
Marketing 2 1 " -split "`n"
$groups = $ObjCol,$ObjCol2 | ForEach-Object {
ConvertFrom-Csv -InputObject $_ -Delimiter ' '} |
Group-Object -Property name
$groups | ForEach-Object {
$name = $_ | Select-Object -ExpandProperty Name
$measure = $_.Group | Measure-Object -Property totalusers,totalinbox -Sum
[PSCustomObject]@{
Name = $name
TotalUsers = $measure[0].sum
TotalInbox = $measure[1].Sum
}} | Format-Table -AutoSize
# Results
# Name TotalUsers TotalInbox
# ---- ---------- ----------
# IT 3 3
# Accounting 6 3
# Marketing 4 2