locked
Where maximum not in RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have a Powershell script where I take the maximum value from the column of an Excel sheet and assign it to a variable.

     $dt_str =  ($dt|Measure-Object -Property "Part_No" -max).Maximum

    It works but I want to modify it to choose where the maximum number is not in the results of a select statement returned from a table. For example if the maximum number found in the sheet is 66912 and 66912 is already in the values returned from the select statement I want the maximum to be the next highest from the column of the sheet. 

    I haven't figured out how to select from a table in Powershell and store the values in an array so I would just be happy to get it to do the above where the "not in" value is a hard-coded array.I don't know the syntax for this would appreciate any help.

    Thank you.

    Edit:

    I think I need to change where I select from the sheet and put a "where not in" clause in that:

    $qry = "select [Part_No] from [{0}]" -f $Table;



    • Edited by blabbermouth Wednesday, February 6, 2019 4:49 PM
    Wednesday, February 6, 2019 4:16 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,
    Thanks for your question.
    You can use PowerShell operator “-contain/-notcontain” or “-in/-notin” to judge Whether in the result.
    -contains: Returns true when reference value contained in a collection
    -notcontains: Returns true when reference value not contained in a collection
    -in: Returns true when test value contained in a collection
    -notin: Returns true when test value not contained in a collection
      Please refer the link below:
    https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_comparison_operators?view=powershell-6
     You can use -first and -skip and “Sort-Object” cmdlet to get the second highest number.
    For example:
    $abc=@(1..15)
    $abc| Sort-Object -Descending  | select -First 1 -Skip 1
    Best Regards,
    Lee

    Just do it.

    Thursday, February 7, 2019 12:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee

    Just do it.

    Thursday, February 21, 2019 6:41 AM