vfpx / fastxtab

A replacement for VFPXTab.prg that creates cross-tab tables and cursors

FastXTab

This project is an upgrade to the FastXTab class, originally created by Alexander Golovlev, by Vilhelm-Ion Praisach. The original class can be downloaded from http://www.universalthread.com/ViewPageNewDownload.aspx?ID=9944.

FastXTab is a replacement for VFPXTab which comes with VFP. It expects to find a table or cursor with at least three columns. By default, the data in the first column becomes the rows in the result, the data in the second column becomes the columns in the result, and the data in the third column is aggregated (summed, by default) to form the data in the result. VFPXTab is quite slow and has limited capabilities. FastXTab is much faster and has a lot more functionality.

All of the source code is containing in FastXTab.prg. There are two folders containing source code and samples:

• FastXTab 1.6 is for VFP 9
• FastXTabs6 1.6 is for VFP 6.

New properties

See the Properties section below for a complete list of properties.

• nAvePrec: precision when using AVE function (DEFAULT=3) The data type is Double precision
• cPageField: allow specifying the field used for page by using either a column name, either an expression
• cRowField: allow specifying the field used for rows by using either a column name, either an expression
• nRowField2: allow distribution by specifiyng cRowField (and cPageField if needed) when nRowField2=0
• cColField: allow specifying the field used for columns by using either a column name, either an expression
• cDataField: allow specifying the field used for cells by using a column name
• nFunctionType: aggregate function 1 Sum 2 Count 3 Avg 4 Min 5 Max 6 Custom (DEFAULT=1 for numeric fields and DEFAULT=5 for nonnumeric fields)
• cFunctionExp: expression when nFunctionType=6
• cCondition: WHERE condition
• cHaving: HAVING condition
• nMultiDataField: if nMultiDataField > 1 the for each column can be defined more DataField / FunctionType / FunctionExp
• anDataField[1],anFunctionType[1],acFunctionExp[1],acDataField[1]: equivalent properties for nDataField, nFunctionType, cFunctionExp, cDataField when nMultiDataField > 1

New behavior

• When EMPTY(cRowField) and nRowField=0 the pivot only distribute the values by columns, according to cDataField, nFunctionType, cFunctionExp and cColField; (values for nFunctionType<> 6 are ignored)
• permission for aggregation functions on non-numeric fields (1 for Sum and 3 for Avg are ignored, Max is by default)

Resulting cursor data types:

1. When EMPTY(cRowField) and nRowField=0 (distribution by columns):

   • same with the field type when nFunctionType<>6
   • taken from results when nFunctionType=6

2. When !EMPTY(cRowField) or nRowField<>0:

   • Integer when nFunctionType=2 (COUNT)
   • Double precision when nFunctionType=3 (AVERAGE); decimal precision given by nAvePrec property
   • taken from results when nFunctionType=6 or nFunctionType=1 (to avoid data overflow)
   • same with the field type in rest

Other upgrades

• improved mdot
• added local variables declaration
• SYS(2015) for internal cursors name

Some examples:

In the Test form in the sample Crosstab project, the Foxite1 method shows solutions for a few recent threads on Foxite. Other examples are posted as comment in the Click method of the cmdFastXtab command button.

1. For http://www.foxite.com/archives/sql-help-0000401315.htm:

   oXTab.cRowField='cstcode'
   oXtab.cColField = 'subj'
   oXtab.nMultiDataField=3
   oXtab.acDataField[1] = 'subj'
   oXtab.anFunctionType[1] = 2
   oXtab.anFunctionType[2] = 6
   oXtab.acFunctionExp[2]="SUM(IIF(attend='P',1,0))"
   oXtab.anFunctionType[3] = 6
   oXtab.acFunctionExp[3]="SUM(IIF(attend='P',1,0))/COUNT(attend)*100"

2. For http://www.foxite.com/archives/row-to-column-0000401353.htm:

   oXtab.nRowField = 0 
   oXtab.cRowField = ''
   oXtab.cColField='ids'
   oXtab.cDataField ='qty'

3. For http://www.foxite.com/archives/split-numbers-2-0000400387.htm:

   oXtab.nRowField = 0 
   oXtab.cRowField = ''
   oXtab.cColField='floor(no/10)+1'
   oXtab.cDataField ='no'

4. For http://www.foxite.com/archives/split-numbers-2-0000400495.htm:

   oXtab.nRowField = 0 
   oXtab.cRowField = ''
   oXtab.cColField='floor(no/100000)+1'
   oXtab.cDataField ='no'

Properties

Property	Description
Input cursor / table
lCloseTable	.T. the cursor / table which holds the data source is closed
Output cursor / table
lCursorOnly	.T. The result is stored in a cursor, otherwise in a free table
cOutFile	Name of the cursor / table which holds the result
lDisplayNulls	.T. / .F. => Set null ON / OFF
lBrowseAfter	Specifies whether to open a Browse window on the cross tab output
CrossTab: a. Rows
cRowField	Field name / Field expression for rows (group)
nRowField	Field position (row number in AFIELDS(,cSource)) for rows (group)
cPageField	Field name / Field expression for rows supergroup (optional)
nPageField	Field position (row number in AFIELDS(,cSource)) for rows supergroup
b. Columns
cColField	Field name / Field expression for columns (group)
nColField	Field position (row number in AFIELDS(,cSource)) for columns (group)
c. Each column field holds a single data (cell) column
cDataField	Field name for cells
nDataField	Field position (row number in AFIELDS(,cSource)) for cells
nFunctionType	Aggregate function used for cells: 1 = Sum, 2 = Count, 3 = Avg, 4 = Min, 5 = Max, 6 = Custom
cFunctionExp	The expression used for cells when nFunctionType=6 (ignored if nFunctionType<>6)
d. Some columns contains more than a single data (cell) column
nMultiDataField	Number of data (cell) columns (default=1)
acDataField	Array with field names for cells
anDataField	Array with field positions (row number in AFIELDS(,cSource)) for cells
anFunctionType	Array with aggregate functions used for cells: 1 = Sum, 2 = Count, 3 = Avg, 4 = Min, 5 = Max, 6 = Custom
acFunctionExp	Array with the expressions used for cells when anFunctionType()=6
e. Miscellaneous
nAvePrec	Decimal precision when nFunctionType = 3 (average)
cCondition	Expression for a where condition
cHaving	Expression for a having condition
nRowField2	When nRowField2 = 0 and !empty(cRowField), FastXTab distribute cells by columns and rows (according to cRowField and cColField). Ignored when nRowField2 <> 0 or empty(This.cRowField)
lTotalRows	When .T. a supplementary row with totals is added

Notes

There are three type of outputs:

1. When nRowField2 = 0 and !empty(cRowField), FastXTab distributes cells by columns and rows (according to cRowField and cColField); no aggregate functions are performed. If nFunctionType / anFunctionType = 6, cells contains the expression from cFunctionExp / acFunctionExp. Otherwise, cells contains the field from cDataField.

2. When nRowField = 0 and EMPTY(cRowField), FastXTab distributes cells by columns (according to cColField); no aggregate functions are performed. If nFunctionType / anFunctionType = 6, cells contains the expression from cFunctionExp / acFunctionExp. Otherwise, cells contains the field from cDataField.

3. Otherwise FastXTab applies aggregate functions and distributes results by columns and rows (according to cPageField, cRowField and cColField).

   • If nFunctionType / anFunctionType = 1, cells contains SUM(cDataField).
   • If nFunctionType / anFunctionType = 2, cells contains COUNT(cDataField).
   • If nFunctionType / anFunctionType = 3, cells contains AVERAGE(cDataField).
   • If nFunctionType / anFunctionType = 4, cells contains MAX(cDataField)
   • If nFunctionType / anFunctionType = 5, cells contains MIN(cDataField)
   • If nFunctionType / anFunctionType = 6, cells contains the expression from cFunctionExp / acFunctionExp (must be valid expression from the point of the aggregation)

fastxtab's Issues

Operator/operand type mismatch error

Hi there!

I'm running fastxtab 1.6 and I'm getting type mismatch error at the line just after this weird one.

It seems the code in lines 7 to 9 bellow is misplaced (or am I missing something?)

1			for lnI = 1 to This.nMultiRowField
2				lcField = InpFields[This.anRowField[m.lnI], 1]
3				if This.nMultiRowField = 1
4					lcValue = rowfld
5				else
6					lcValue = evaluate('rowfld' + transform(lnI))
7				endif _This.nMultiRowField > 1
8				rowfldvalue = rowfldvalue + iif(empty(rowfldvalue), '', ',') + ;
9					lcValue
10				if lnI = 1
11					insert into (m.cOutStem) ((lcField)) values (lcValue)
12				else
13					replace (lcField) with lcValue in (m.cOutStem)
14				endif
15			next

Here is the bit of code I'm using to test Fastxtab:

Local oFastX As fastxtab
oFastX = NEWOBJECT('fastxtab',[fastxtab.prg])
With oFastX As fastxtab
   .cOuTFILE = [curTemp]
   .lCuRSORONLY = .T.
   .CROWFIELD = [user_id]   
   .cCOLFIELD = [YEAR(sale_date)]
   .cDATAFIELD = [total]        
   .runxtab()
Endwith

Summed totals for rows

Is it possible to get a summed total of the values in the rows? (i.e. a horizontal total for each row). I can see that if the value for 'lTotalRows' is set to True, that you get an additional row at the end of the data in the xtab showing the totals for the (vertical) columns. Is there a similar function to get a total of the (horizontal) rows? For example, the existing VFPXTAB routine has an true/false option for totaling the rows as the 8th parameter.

PS. Have tested FastXtab on some local clients data and it is noticeably faster so would really like to use this updated utility (assuming that row totals are also possible).