Get maximum percent from csv

Question

subject score studentid
xx   23.22% 1
yy  34% 2
zz  55.2%   3
xx  88.66%  4
yy  23.76%  5
zz  78.04%  6

How to get max % and student id for each subject?

Answer 1

here's a vbscript you can try

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
Set d = CreateObject("Scripting.Dictionary")
Set e = CreateObject("Scripting.Dictionary")
strFile = objArgs(0)
Set objFile = objFS.OpenTextFile(strFile)
Do Until objFile.AtEndOfStream
    strLine=objFile.ReadLine
    s = Split(strLine," ")
    subject =s(0)
    score= Left(s(1),Len(s(1))-1)
    studentid=s(2)
    If Not d.Exists(subject) Then
        d.Add subject, score
        e.Add subject, studentid
    Else
        If score >= d.Item(subject)  Then           
            d.Item(subject) = score
            e.Item(subject) = studentid
        End If
    End If
Loop
j=d.Keys
For Each stritems In j
    WScript.Echo "Subject:"&stritems & ", Score: "& d.Item(stritems) & "%, StudentID: " & e.Item(stritems)
Next

output

C:\test>type file
xx 23.22% 1
yy 34% 2
zz 55.2% 3
xx 88.66% 4
yy 23.76% 5
zz 78.04% 6

C:\test>cscript //nologo test.vbs file
Subject:xx, Score: 88.66%, StudentID: 4
Subject:yy, Score: 34%, StudentID: 2
Subject:zz, Score: 78.04%, StudentID: 6

Answer 2

You'll need to have some sort of collection to handle the totals for each subject, this might help

@echo off 

  set CountXX=1
  set CountYY=2


  set This=XX
  call :Resolve Count%This%
  echo %RetVal%


  set This=YY
  call :Resolve Count%This%
  echo %RetVal%

  set /a Count%This%=%RetVal% + 2 

  call :Resolve Count%This%
  echo %RetVal%

goto :eof


:Resolve


  for /f "delims== tokens=2" %%a in ('set %1') do set retval=%%a

goto :eof