SORT Order not working correction

[SIMMS7400]

HI Folks -

For some reason, the SORT function is not working as I would expect. I am sorting the 5th column which has values from 1 through 12. However, when I do SORT /R it's saying the max is9, not 12.

Here is my code:

Code: Select all

	(  
		FOR %%F IN ("!DATAFILENAME!") DO (

		  REM Sort PERIOD Column in reverse order to get MAX value
			SET "MAX="
			SET "MIN="
			FOR /F "tokens=5 delims=|" %%A IN ('SORT /R "%%~F"') DO (
				IF NOT DEFINED MAX (
					SET "MAX=%%A"
				 )
			)
			
			REM Sort PERIOD Column in regular order to get MIN value
			FOR /F "tokens=5,6 delims=|" %%A IN ('SORT "%%~F"') DO (
				IF NOT DEFINED MIN (
					SET "MIN=%%A"
					SET "YEAR=%%B" & SET "YEAR=!YEAR:~2,2!"
				 )
			)
	   )
	)
POPD

echo !MIN! 
echo !MAX!
echo !YEAR!
pause

Here is data file

AC_41110|FH_00000|DP_000|CO_11|1|FY19|budget|-3344033.0
AC_41110|FH_00000|DP_000|CO_11|2|FY19|budget|-3464968.18
AC_41110|FH_00000|DP_000|CO_11|3|FY19|budget|-3734198.0
AC_41110|FH_00000|DP_000|CO_11|4|FY19|budget|-3743092.36
AC_41110|FH_00000|DP_000|CO_11|5|FY19|budget|-3492925.18
AC_41110|FH_00000|DP_000|CO_11|6|FY19|budget|-3517093.27
AC_41110|FH_00000|DP_000|CO_11|7|FY19|budget|-3762019.55
AC_41110|FH_00000|DP_000|CO_11|8|FY19|budget|-3770729.82
AC_41110|FH_00000|DP_000|CO_11|9|FY19|budget|-3477456.18
AC_41110|FH_00000|DP_000|CO_11|10|FY19|budget|-3942433.64
AC_41110|FH_00000|DP_000|CO_11|11|FY19|budget|-3289566.64
AC_41110|FH_00000|DP_000|CO_11|12|FY19|budget|-2968880.0

Do you know why? Thanks!

[Aacini]

Of course! SORT command sorts lines. In other words, sorts strings, and

Code: Select all

AC_41110|FH_00000|DP_000|CO_11|9|FY19|budget|-3477456.18

string is "larger" (that is, goes after) than

Code: Select all

AC_41110|FH_00000|DP_000|CO_11|12|FY19|budget|-2968880.0

string (because the "9" is larger than the "1").

Antonio

[SIMMS7400]

HI Antonio -


Hmmm - I see and now understand. What would be my path forward to overcome this hurdle? I tried to strip out just that column but no luck. Can this still even be done?

[Jer]

Here's my solution which is to create an artificial sort field. I'm looking forward to reading Antonio's solution.
I rearranged lines in the source text to show that it works.

Code: Select all

@echo off
setlocal EnableDelayedExpansion
set "srce=source.txt"
set "del=|"
>_temp.txt (
  For /F "tokens=1-6* delims=%del%" %%a In (%srce%) Do (
    set "dy=%%e" & set "dy=0!dy!"
    set "dy=!dy:~-2!"
    echo !dy!~%%a^%del%%%b^%del%%%c^%del%%%d^%del%%%e^%del%%%f^%del%%%g
  )
)
sort _temp.txt>tmpsort.txt
>newsource.txt (For /F "tokens=2* delims=~" %%a In (tmpsort.txt) Do echo %%a)
echo source.txt contents: & type source.txt &echo(
echo newsource.txt contents:
type newsource.txt & DEL tmpsort.txt & DEL _temp.txt
endlocal & exit /b

source.txt contents:
AC_41110|FH_00000|DP_000|CO_11|12|FY19|budget|-2968880.0
AC_41110|FH_00000|DP_000|CO_11|1|FY19|budget|-3344033.0
AC_41110|FH_00000|DP_000|CO_11|2|FY19|budget|-3464968.18
AC_41110|FH_00000|DP_000|CO_11|3|FY19|budget|-3734198.0
AC_41110|FH_00000|DP_000|CO_11|4|FY19|budget|-3743092.36
AC_41110|FH_00000|DP_000|CO_11|5|FY19|budget|-3492925.18
AC_41110|FH_00000|DP_000|CO_11|10|FY19|budget|-3942433.64
AC_41110|FH_00000|DP_000|CO_11|6|FY19|budget|-3517093.27
AC_41110|FH_00000|DP_000|CO_11|7|FY19|budget|-3762019.55
AC_41110|FH_00000|DP_000|CO_11|8|FY19|budget|-3770729.82
AC_41110|FH_00000|DP_000|CO_11|11|FY19|budget|-3289566.64
AC_41110|FH_00000|DP_000|CO_11|9|FY19|budget|-3477456.18

newsource.txt contents:
AC_41110|FH_00000|DP_000|CO_11|1|FY19|budget|-3344033.0
AC_41110|FH_00000|DP_000|CO_11|2|FY19|budget|-3464968.18
AC_41110|FH_00000|DP_000|CO_11|3|FY19|budget|-3734198.0
AC_41110|FH_00000|DP_000|CO_11|4|FY19|budget|-3743092.36
AC_41110|FH_00000|DP_000|CO_11|5|FY19|budget|-3492925.18
AC_41110|FH_00000|DP_000|CO_11|6|FY19|budget|-3517093.27
AC_41110|FH_00000|DP_000|CO_11|7|FY19|budget|-3762019.55
AC_41110|FH_00000|DP_000|CO_11|8|FY19|budget|-3770729.82
AC_41110|FH_00000|DP_000|CO_11|9|FY19|budget|-3477456.18
AC_41110|FH_00000|DP_000|CO_11|10|FY19|budget|-3942433.64
AC_41110|FH_00000|DP_000|CO_11|11|FY19|budget|-3289566.64
AC_41110|FH_00000|DP_000|CO_11|12|FY19|budget|-2968880.0

[SIMMS7400]

Thank you! Let me play around with this and see if I can get it to work. Ultimately, I need MIN and MAX, I can adjust and see what it renders.

Thanks!

[Aacini]

Are you using SORT not for sorting, but to get maximum and minimum? And two times? Strange thing...

Why not keep things simple? Try:

Code: Select all

@echo off
setlocal

set /A max=-99999, min=99999
for /F "usebackq tokens=5,6 delims=|" %%a in ("%%~F") do (
   if %%a gtr !max! set "max=%%a"
   if %%a lss !min! set "min=%%a" & set "year=%%b"
)


echo !min!
echo !max!
echo !year:~2,2!

Antonio

[aGerman]

I absolutely agree with Antonio in this case.
Once you really need a logical sorting you can use this hybrid macro:
Sort logically like in Windows Explorer (Powershell hybrid)

Steffen

[Eureka!]

And yet another way:

If you have cygwin or Linux Subsystem for Windows (*) installed, you can do:

Code: Select all

c:\Tools\cygwin64\bin\sort --field-separator  "|" +4 --numeric-sort filename.txt

or short: sort -t "|" +4 -n sort filename.txt
(sort by field 4 (first=0) ; use natural sort)


(*) or whatever it is called this month ..

[SIMMS7400]

Are you using SORT not for sorting, but to get maximum and minimum? And two times? Strange thing...

Why not keep things simple? Try:

Code: Select all

@echo off
setlocal

set /A max=-99999, min=99999
for /F "usebackq tokens=5,6 delims=|" %%a in ("%%~F") do (
   if %%a gtr !max! set "max=%%a"
   if %%a lss !min! set "min=%%a" & set "year=%%b"
)


echo !min!
echo !max!
echo !year:~2,2!

Antonio

Antonio - that worked perfectly! I was making it much harder than it needed to be.

Thanks again for your help as always!!!