CODEWITHSUNDEEP
powershell
Miha Žitko
Miha Žitko

Reputation: 83

Powershell - compare two arrays and write extra data from second array to first

Each month I get a report from our phone provider with complete specs of all costs. Out of received data I wan't to create a report about phone usage for each phone user. I solved this in Excell, but want to make a better solution with Powershell. But... problems...

One of the columns contains data like subscriptions, call types etc. In all there are about 100 different types of data in that column, but I want to sort them in 4 different categories.

I have a list that contains all the types of data and categories in another file but I can't find a way to check data from first list compare them to the second one and write categories back in first array as extra data. In Excel I used lookup function for this.

Below an example of what I want: I have some data:

  $data1 = @(
   [pscustomobject]@{No='1';Name='Joe';Service='Subscription 1';Price='5'}
   [pscustomobject]@{No='2';Name='Sue';Service='Subscription 2';Price='4'}
   [pscustomobject]@{No='3';Name='Joe';Service='Call Price 1';Price='0,1'}
   [pscustomobject]@{No='4';Name='Sue';Service='Call Price 2';Price='0,2'}
   [pscustomobject]@{No='5';Name='Sue';Service='Call Price 1';Price='0,1'}
   [pscustomobject]@{No='6';Name='Joe';Service='Call Price 2';Price='0,2'})

And a list of categories:

  $data2 = @(
   [pscustomobject]@{Service='Subscription 1';Overview='Subscriptions'}
   [pscustomobject]@{Service='Subscription 2';Overview='Subscriptions'}
   [pscustomobject]@{Service='Call Price 1';Overview='Calls'}
   [pscustomobject]@{Service='Call Price 2';Overview='Calls'}
   [pscustomobject]@{Service='Call Price 1';Overview='Calls'}
   [pscustomobject]@{Service='Call Price 2';Overview='Calls'})

And I want an output that combines it together by Service:

No       : 1
Name     : Joe
Service  : Subscription 1
Overview : Subscriptions
Price    : 5

No       : 2
Name     : Sue
Service  : Subscription 2
Overview : Subscriptions
Price    : 4

No       : 3
Name     : Joe
Service  : Call Price 1
Overview : Calls
Price    : 0,1

No       : 3
Name     : Sue
Service  : Call Price 2
Overview : Calls
Price    : 0,2

No       : 3
Name     : Sue
Service  : Call Price 1
Overview : Calls
Price    : 0,1

No       : 3
Name     : Joe
Service  : Call Price 2
Overview : Calls
Price    : 0,2

Thanks for your help!

Upvotes: 0

Views: 244

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174485

Start by creating a dictionary from the data in $data2:

$serviceTypes = @{}

$data2 |ForEach-Object { $serviceType[$_.Service] = $_.Overview }

Now you can use Select-Object to resolve the service category with a single lookup:

$usageData = $data1 |Select *,@{Name='Overview';Expression={$serviceType[$_.Service]}}

Upvotes: 1

Related Questions