Faceți căutări pe acest blog

luni, 2 februarie 2015

FastXTab version 1.6

A crosstab class.
This is an enhanced version of the FastxTab class, created by Alexander Golovlev.
The original class can be found here  http://www.universalthread.com/ViewPageNewDownload.aspx?ID=9944 
There are many extensions, like formula for every element and multiple cells.

Download from :
Properties
1 Input cursor / table
- lCloseTable
.T. the cursor / table which holds the data source is closed

2 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

3 CrossTab
a) Rows
- cRowField
Field name / Field expression for rows (group)
- nRowField
Field position (row number in AFIELDS(,cSource)) for rows (group)

- cPageField (optional)
Field name / Field expression for rows supergroup
- 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) When 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 (default=1)
Number of data (cell) columns
- 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) Miscelaneous
- 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 (accoding 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 distribute cells by columns and rows (accoding 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 distribute cells by columns (accoding 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 distribute results by columns and rows (accoding 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 a valid expression from the point of the aggregation)

Examples
Demo 1
A table with inputs and outputs from some companies for each year.

RAND(-1)
CREATE CURSOR cTest (cName C(10),nYear I,nInput N(10,2),nOutput N(10,2))
FOR lnC=1 TO 5
    FOR lnY=2010 TO 2014
        INSERT INTO cTest VALUES ('Company '+TRANSFORM(m.lnC),m.lnY,100000*RAND(),100000*RAND())
    NEXT
NEXT

BROWSE


1 Get inputs
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && cells contains Inputs

oXtab.RunXtab()


Result is a table with a random name

2  Get inputs and show (browse) the output
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && Inputs

oXtab.lBrowseAfter = .T.

oXtab.RunXtab()


3 Choose a name for the output table
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && Inputs
oXtab.lBrowseAfter = .T.

oXtab.cOutFile="xx" && table is named xx

oXtab.RunXtab()


4 Output into a cursor rather than a table
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && Inputs
oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"

oXtab.lCursorOnly = .T.

oXtab.RunXtab()


5 Show null values (if exists)
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && cells contains Inputs
oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.

oXtab.lDisplayNulls = .F.

oXtab.RunXtab()


6 Close the input table after run
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && cells contains Inputs
oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.

oXtab.lCloseTable = .F.

oXtab.RunXtab()


7 Add a supplementary row with total
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && cells contains Inputs
oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


8 Show both nInput and nOutput
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years

oXtab.nMultiDataField = 2
oXtab.acDataField[1] = 'nInput' && cells contains Inputs
oXtab.acDataField[2] = 'nOutput' && cells contains Outputs

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


9 Show three columns : nInput, nOutput and nInput - nOutput
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years

oXtab.nMultiDataField = 3
oXtab.acDataField[1] = 'nInput' && cells contains Inputs
oXtab.acDataField[2] = 'nOutput' && cells contains Outputs
oXtab.anFunctionType[3] = 6
oXtab.acFunctionExp[3] = 'SUM(nInput - nOutput)'

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


* Note the use of SUM()

Demo 2
A table with inputs and outputs from some companies for each month of the each year. Some values are missing

RAND(-1)
CREATE CURSOR cTest (cName C(10),nYear I,nInput N(10,2),nOutput N(10,2))
FOR lnC=1 TO 5
    FOR lnY=2010 TO 2014
        INSERT INTO cTest VALUES ('Company '+TRANSFORM(m.lnC),m.lnY,100000*RAND(),100000*RAND())
    NEXT
NEXT

BROWSE


1 Get total / year (inputs)
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && cells contains Inputs
oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


2 Get average / mounth (inputs)
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && cells contains Inputs

oXtab.nFunctionType = 3

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


3 Count the mounths with data for each company in every year (inputs)
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && cells contains Inputs

oXtab.nFunctionType = 2

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


4 Get worst (minimum) monthly input value from each year / each company
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && cells contains Inputs

oXtab.nFunctionType = 4

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


5 Get the best (maximum) monthly input value from each year / each company
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names
oXtab.cColField = 'nYear' && columns are years
oXtab.cDataField = 'nInput' && cells contains Inputs

oXtab.nFunctionType = 5

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


6 Get total / semester (inputs)
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names

oXtab.cColField = 'PADL(nYear,4)+[_sem]+iif(nMonth<7,[1],[2])' && columns are semesters

oXtab.cDataField = 'nInput' && cells contains Inputs
oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


7 Get quarterly average for nInputs - nOutputs
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cRowField = 'cName' && rows are company names

oXtab.cColField = 'PADL(nYear , 4) + [_qtr] + PADL(1 + FLOOR((nMonth - 1) / 3) , 1)' && columns are quarters
oXtab.nFunctionType = 6
oXtab.cFunctionExp = 'AVG(nInput - nOutput)'

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


8 Get quarterly average for nInputs - nOutputs, but columns are companies, and rows are quarters
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cPageField = 'nYear' && 'pages' are years
oXtab.cRowField = '1 + FLOOR((nMonth - 1) / 3)' && rows are quarters
oXtab.cColField = 'cName' && columns are company names

oXtab.nFunctionType = 6
oXtab.cFunctionExp = 'AVG(nInput - nOutput)'

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


9 Get quarterly average for nInputs - nOutputs, columns are companies, and rows are quarters but only for quarters with results < 0
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cPageField = 'nYear' && 'pages' are years
oXtab.cRowField = '1 + FLOOR((nMonth - 1) / 3)' && rows are quarters
oXtab.cColField = 'cName' && columns are company names
oXtab.nFunctionType = 6
oXtab.cFunctionExp = 'AVG(nInput - nOutput)'

oXtab.cHaving = 'AVG(nInput - nOutput) < 0' && Having

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


10 Get quarterly average for nInputs - nOutputs, columns are companies, and rows are quarters, for quarters with results < 0 but only for first company
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cPageField = 'nYear' && 'pages' are years
oXtab.cRowField = '1 + FLOOR((nMonth - 1) / 3)' && rows are quarters
oXtab.cColField = 'cName' && columns are company names
oXtab.nFunctionType = 6
oXtab.cFunctionExp = 'AVG(nInput - nOutput)'

oXtab.cHaving = 'AVG(nInput - nOutput) < 0' && Having
oXtab.cCondition = 'cName==[Company 1]'     && Where

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.
oXtab.lDisplayNulls = .F.
oXtab.lCloseTable = .F.
oXtab.lTotalRows = .T.

oXtab.RunXtab()


Demo 3
A table with athletes and their training results

RAND(-1)
CREATE CURSOR cTest (cName C(10),nResult N(8,2))
FOR lnA=1 TO 5
    FOR lnR=1 TO 20
        IF RAND()>0.2
            INSERT INTO cTest VALUES ('Athlete '+TRANSFORM(m.lnA),100*RAND())
        ENDIF
    NEXT
NEXT

BROWSE


1 Group each athlete's results by tens of seconds
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cPageField = ''
oXtab.cRowField = 'cName' && rows are athletes
oXtab.nRowField2 = 0 && distribution

oXtab.cColField = 'floor(nResult/10)' && columns are tens results
oXtab.cDataField = 'nResult' && cells are results


oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.

oXtab.RunXtab()


2  Group the results by tens of seconds, regardless whose are
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cPageField = '' && 'pages' are years
oXtab.cRowField = '' && rows are irrelevant
oXtab.nRowField = 0 && distribution

oXtab.cColField = 'floor(nResult/10)' && columns are tens results
oXtab.cDataField = 'nResult' && cells are results


oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.

oXtab.RunXtab()


3 Group each athlete's results by tens of seconds, In each column, only the fraction is shown
Local oXtab
oXtab = NewObject("FastXtab", "fastxtab.prg")

oXtab.cPageField = ''
oXtab.cRowField = 'cName' && rows are athletes
oXtab.nRowField2 = 0 && distribution

oXtab.cColField = 'floor(nResult/10)' && columns are tens results

oXtab.nFunctionType = 6
oXtab.cFunctionExp = 'nResult/10 - floor(nResult/10)'

oXtab.lBrowseAfter = .T.
oXtab.cOutFile="xx"
oXtab.lCursorOnly = .T.

oXtab.RunXtab()

4 comentarii: