Find Max and Min value for a particular key

This topic contains 5 replies, has 4 voices, and was last updated by  Max Kozlov 4 months ago.

  • Author
    Posts
  • #68610

    Adarsh
    Participant

    Hi All,

    How can I find the max and min value from below data ?

    Data below.

    Adarsh, 50

    Nitin, 69

    Nitin, 72

    Rohit, 23

    Adarsh, 90

    Bumrah, 312

    Rohit, 45

    Bumrah, 69

    Rohit, 79

    Adarsh, 87

    Nitin, 99

    Atul, 50

    I want to display data in below format

    Adarsh, 90, 50

    Atul, 50

    Nitin, 99, 69

    Bumrah, 312, 69

    Rohit 79, 23

  • #68614

    Olaf Soyk
    Participant

    Actually that's not a free script shop here. What did you try so far?

  • #68617

    Michael
    Participant

    Hey Adarsh,

    I am not sure if this will help out at all, but you can try this. Copy your data to a .csv file with your data matching format below. The data would be separated by rows and columns. DO NOT PUT ALL DATA INTO A ROW.

    Name  Value
    Nitin 20
    Nitin 30
    David 20
    ...
    

    Then run this. It sorted the people names by name and value. I couldn't figure out a way to sort it by value only or to combine the values, but the output still may help.

    # Load CSV from your location
    $coll = Import-CSV "C:\Temp\Book1.csv"
    
    # Create table
    $dtPeople = New-Object System.Data.DataTable("people")
    $cols = @("name","value")
    
    # Schema (columns)
    foreach ($col in $cols) {
    	$dtPeople.Columns.Add($col) | Out-Null
    }
    
    # Values (rows)
    foreach ($c in $coll) {
    	$row = $dtPeople.NewRow()
    	foreach ($col in $cols) {
    		$row[$col] = $c.$col
    	}
    	$dtPeople.Rows.Add($row) | Out-Null
    }
    
    
    # DataView rapid filter
    $dvPeople	= New-Object System.Data.DataView($dtPeople)
    
    $dvPeople | Sort-Object -Property name
    

    I did not write this PS script myself. I used it from "ProvidingCredit=@SPJeff,Title=DataTable in PowerShell for crazy fast filters." If you google what I provided there you can view his blog post & code.

    Good luck!

  • #68710

    Max Kozlov
    Participant

    Without explanation, ok ? 🙂

    'Adarsh, 50
    Nitin, 69
    Nitin, 72
    Rohit, 23
    Adarsh, 90
    Bumrah, 312
    Rohit, 45
    Bumrah, 69
    Rohit, 79
    Adarsh, 87
    Nitin, 99
    Atul, 50'-split"`n"|convertfrom-csv -d ','-H 1,0|group '1'|%{$1=$_.group;$1.0|measure -Mi -Ma|select @{n='Name';e={$1[0].1}},M*}
    
  • #68731

    Michael
    Participant

    Hey Max,

    I enjoyed decoding your PowerShell. I didn't know you could pass in your data like that into the "Convertfrom-CSV" cmdlet. I learned something new today. Your approach for solving this was by far much easier and smarter then my earlier response.

    My explanation below.

    'Adarsh, 50
    Nitin, 69
    Nitin, 72
    Rohit, 23
    Adarsh, 90
    Bumrah, 312
    Rohit, 45
    Bumrah, 69
    Rohit, 79
    Adarsh, 87
    Nitin, 99
    Atul, 50'-split"`n" | `
    convertfrom-csv -Delimiter ','-Header 1,0 | `  #Set Delimiter as "," to separate and provide headers 1,0
    group '1' | `  #Group values by column 1 which is by Name
    ForEach-Object {$1=$_.group;$1.0|measure -Minimum -Maximum|Select @{n='Name';e={$1[0].1}},M*}  ## Foreach Object Measure Min & Max then Select Min & Max by name and output
    
  • #68739

    Max Kozlov
    Participant

    If you like it you can see this topic

    It's a pity that great idea did not get much development, even from author.
    but it offtopic here 🙂

You must be logged in to reply to this topic.