Forum Discussion

John_Dodo's avatar
John_Dodo
Copper Contributor
May 03, 2023

retrieve rows with highest values in from array

Hi,

 

I'm struggling a bit with something that should be easy ...

I import a CSV and then I want to retrieve the rows with highest value for different "families".

Ex:

col1;col2;col3;col4

dateA;child1;son1;4

dateB;child2;son2;1

dateC;child2;son1;5

dateD;child1;son1;2

dateE;child1;son3;4

dateF;child1;son3;3

dateG;child2;son2;4

 

In here, I would like to get an new array with only

col2;col3;col4

familly1;child1;4

familly1;child3;4

familly2;child2;4

familly2;child1;5

 

I have tried doing a foreach in a foreach but I'm still struggling with the two level depth of family and child

 

Here is what I started to do :

$tab = Import-Csv -Path $file -Delimiter ";" -Header "col1","col2", "col3"
#This sort is probably not needed but I'm trying...
$tabsorted = $tab | sort-object -property @{expression = "col2"; Descending = $true }, @{expression = "col3"; Descending = $true }, @{expression = "col4"; Descending=$true}
 
$dummyfamilies= $tab | Select-Object -property "col2","col3" -Unique

    $tabtemp = @{}
    $count = 0
    foreach($familly in $dummyfamilies) {
        foreach ($row in $tabsorted){
        #this is the painful part
          if( (( $row.col2 = $family.col2) -and $row.col3 = $family.col3) -and #here i'm lost ($tabtemp.col2.col3.Maxvalue -gt $family.col4)){
          $tabtemp = @{family = $row.col2; son = $family.col3; Maxvalue = $row.col4} # something like this...
          }
       }
    }

 

Well something like this.

 

Thank you for your help.

 

powershell

1 Reply

Sort By
  • LainRobertson's avatar
    LainRobertson
    Silver Contributor
    May 15, 2023

    John_Dodo 

     

    I don't actually understand the data relationship between table 1 and 2 - specifically, I don't know where "family1", "family2", etc. are coming from in table 2, col2.

     

    For the sake of providing an example, I'm going to assume that table 1, col3 ("son1", "son2", etc.) equates to "family1", "family2", etc in table 2, col2.

     

    If that's not the case, then you'll need to better-explain how to get from table 1 to table 2.

     

    If it is the case, then I've used the following example along with your data from table 1 to produce the output below the script.

     

    Script

    $Hashcode = 0;
$NewHashCode;
$LastSon = "";
$Index = 0;

Import-Csv -Path D:\Data\Temp\Forum\forums.txt -Delimiter ";" |
    Sort-Object -Property col3, col2, @{ e = "col4"; d = $true} |
        ForEach-Object {
            if ($HashCode -ne ($NewHashCode = [string]::Concat($_.col3, $_.col2).GetHashCode()))
            {
                $HashCode = $NewHashCode;

                if ($LastSon -ne $_.col3)
                {
                    $LastSon = $_.col3;
                    $Index++;
                }

                [PSCustomObject] @{
                    col2 = "family$Index";
                    col3 = $_.col2;
                    col4 = $_.col4;
                }
            }
        }

     

    Sample output

     

    Cheers,

    Lain

Resources