Pivot table from listviews or other sources

[radcki]

I'm working with lots of database dump data displayed on listviews without built in analytical functions, so I've created fairly simple function using dynamic variables to generate summary similar to pivot table in excel. I have not seen anything like this posted when searching, so here you go

Function code (varize function is slight mod of one posted here: https://autohotkey.com/board/topic/9430 ... -a-string/ ):

Code: Select all

pivot(kol_grupa = 1, kol_suma = 2, lv_items="")
{
	;list parser
	Loop, Parse, lv_items, `n  ; rows loop
	{
		all_count ++ ; row counter
		Loop, Parse, A_LoopField, %A_Tab%  ; columens loop
		{			
			IF (a_index = kol_grupa)
			{
				grupa := varize(A_LoopField) ; workaround for variable name limitations
				%grupa%name := A_LoopField ; actual group name
				%grupa%_lines ++ ; group row counter
			}
			
			IF (a_index = kol_suma)
			{
				%grupa%_sum += A_LoopField
				all_sum += A_LoopField
			}			
		}
			
		if (%grupa%_id = "") ; constructor of pseudo-array containing group names 
		{
			n ++
			%grupa%_id = %grupa%name
			grupa%n% := %grupa%name
		}		
	}
	
	; summary generation
	loop % n
	{
		grupa_name := grupa%A_index%
		grupa := varize(grupa_name)
		
		line := grupa_name . A_Tab . %grupa%_lines . A_Tab . %grupa%_sum . A_Tab . format("{1:0.2f}", %grupa%_sum/%grupa%_lines) . A_Tab . format("{1:0.2f}", (%grupa%_lines/all_count)*100) . "%" . A_Tab . format("{1:0.2f}", (%grupa%_sum/all_sum)*100) . "%"
		if (A_index=1)
			final .= line	
		else
			final .= "`n" . line
	}
	final .= "`n" . "TOTAL" . A_Tab . all_count . A_Tab . all_sum . A_Tab . format("{1:0.2f}", all_sum/all_count) . A_Tab . format("{1:0.2f}", (all_count/all_count)*100) . "%" . A_Tab . format("{1:0.2f}", (all_sum/all_sum)*100) . "%"

	return final
}

varize(var)
{
   stringreplace,var,var,%A_space%,_,a
   chars = .,<>:;'"/|\(){}=-+!`%^&*~
   loop, parse, chars,
      stringreplace,var,var,%A_loopfield%,_,a
   return var
}

1st parameter of function is column for grouping, second is column to be summed and third one is input data - columns should be tab delimited.

Functional example below, to use it you must load semicolon delimited csv file and then press ctr+w while mouse is over populated listview. Result is parsed into listview, in my actual use of function I've also added button to export data to excel, for easy copy-pase
Code:

Code: Select all

#MaxMem 4095

Gui, font, s13 q5
Gui, Add, Text, w500 Center, File:
Gui, font, s11 q5
Gui, add, edit, Disabled yp+30 xs+5 w300 vvEdit, %plik%
Gui, Add, Button,gBrowse yp+0 xp+320 w130 h25 , Select CSV
Gui, Add, Text, x10 , How many times to read file:
Gui, add, edit, yp+0 xs+200 w50 vloops, 1

gui, font, s10 q5
Gui, Add, Text, x10 w500 Center, DATA:
Gui, Add, ListView,grid r10 w500 +hscroll vLV, GROUP|UNITS
LV_ModifyCol(1,366)
LV_ModifyCol(2,100)
counter := LV_GetCount()
gui, Add, Text, vLv_count w500, List count: %counter%
Gui, Show,,Input
Return

^w::
tooltip, Counting...
MouseGetPos,,, lvwindow, lvcontrol ; get list from under the mouse
ControlGet, lv_items, List,,%lvcontrol%, ahk_id %lvwindow%	; get list text
pv := pivot(1,2,lv_items)
ToolTip

gui,2:Add,Listview,vLista grid w450+hscroll r10,GROUP|COUNT|SUM|AVG|`% of total (count)|`% of total (sum)|
Gui,2:default
LV_ModifyCol(1, "100")
LV_ModifyCol(2, "65")
LV_ModifyCol(3, "65")
LV_ModifyCol(4, "65")
LV_ModifyCol(5, "65")
LV_ModifyCol(6, "65")
;function result parsing
Loop, Parse, pv, `n  ; rows loop
{
	Loop, Parse, A_LoopField, %A_Tab%
		var%A_index% := A_LoopField	
	lv_add(, var1, var2, var3, var4, var5, var6)
	
	loop, 6
		var%A_index% := ""
}
gui, 2:Show,,Pivot
return

Browse:
Gui, submit, NoHide
FileSelectFile, plik, 3, , Wybierz plik, CSV (*.csv)
if plik =
	Return
GuiControl,,vEdit,%plik%
GuiControl,,Lv_count, List count: (loading in progress)
Progress, b w200,%a_space%, Loading file, Progress
GUI, ListView, LV
loop % loops
{
	Progress % format("{1:0.0f}", (A_index/loops)*100),% format("{1:0.1f}", (A_index/loops)*100) . "%"
	loop,read,%plik%
	{
		stringsplit,BX,A_LoopReadLine,`;,	
		LV_Add(,BX1,BX2)
		BX1=
		BX2=
	}
}
Progress, Off
counter := LV_GetCount()
GuiControl,,Lv_count, List count: %counter%
Return

pivot(kol_grupa = 1, kol_suma = 2, lv_items="")
{
	;list parser
	Loop, Parse, lv_items, `n  ; rows loop
	{
		all_count ++ ; row counter
		Loop, Parse, A_LoopField, %A_Tab%  ; columens loop
		{			
			IF (a_index = kol_grupa)
			{
				grupa := varize(A_LoopField) ; workaround for variable name limitations
				%grupa%name := A_LoopField ; actual group name
				%grupa%_lines ++ ; group row counter
			}
			
			IF (a_index = kol_suma)
			{
				%grupa%_sum += A_LoopField
				all_sum += A_LoopField
			}			
		}
			
		if (%grupa%_id = "") ; constructor of pseudo-array containing group names 
		{
			n ++
			%grupa%_id = %grupa%name
			grupa%n% := %grupa%name
		}		
	}
	
	; summary generation
	loop % n
	{
		grupa_name := grupa%A_index%
		grupa := varize(grupa_name)
		
		line := grupa_name . A_Tab . %grupa%_lines . A_Tab . %grupa%_sum . A_Tab . format("{1:0.2f}", %grupa%_sum/%grupa%_lines) . A_Tab . format("{1:0.2f}", (%grupa%_lines/all_count)*100) . "%" . A_Tab . format("{1:0.2f}", (%grupa%_sum/all_sum)*100) . "%"
		if (A_index=1)
			final .= line	
		else
			final .= "`n" . line
	}
	final .= "`n" . "TOTAL" . A_Tab . all_count . A_Tab . all_sum . A_Tab . format("{1:0.2f}", all_sum/all_count) . A_Tab . format("{1:0.2f}", (all_count/all_count)*100) . "%" . A_Tab . format("{1:0.2f}", (all_sum/all_sum)*100) . "%"

	return final
}

varize(var)
{
   stringreplace,var,var,%A_space%,_,a
   chars = .,<>:;'"/|\(){}=-+!`%^&*~
   loop, parse, chars,
      stringreplace,var,var,%A_loopfield%,_,a
   return var
}


2GuiClose:
Gui, 2: destroy
Return

GuiClose:
ExitApp
Return

Sample CSV:

Code: Select all

AA Aa;12
AA Aa;23
AA Aa;1
AA Aa;25
AA Aa;4
AA Aa;36
BB BB b;2
CC ccCC;84
CC ccCC;1
BB BB b;2
BB BB b;1
BB BB b;3
BB BB b;23
BB BB b;2
BB BB b;6
BB BB b;4
BB BB b;8
BB BB b;2

Function could be easily used to read data from other sources, although for now I'm not sure how to make column delimiter a function parameter and keep support for %A_tab% delimiter. Anyway I'm sure there is a lot of room for improvement, feel free to do so and share your results