MdTable

What's new in Analytica 4.0? >

MdTable(T, Rows, Cols,Vars, conglomerationFn, defaultValue, valueColumn)

In the default case, it converts a 2D relational table, T, indexed by Rows and Cols, into an N-dimensional array result. Given M=Size(Cols), N = M - 1. Each cell in thee result is the sum over the values in the last column of T for all rows whose coordinates correspond to the index values of the result. All except the last column in Cols of T are interpreted as coordinates. Vars should be a list of identifiers of (as text) or handles to the Indexes to be used in the result.

MDTable is analogous to PivotTable in Excel. It is the inverse of MDArrayToTable().

This default behavior can be modified by a number of optional parameters.

MdTable interprets the first N columns (along the Cols index) as containing a coordinate in the final multi-dimensional array, and last remaining M columns as containing values (in OLAP terminology, measures), where N+M = Size(Cols). In the most common usage, M=1 and N=Size(Cols-1), meaning that all columns except the last specify the coordinate, and the last column contains the cell value. In this case, the Vars and valueColumn parameters are optional. If the Vars parameter is specified, its length determines how many columns are used for coordinates, and if its length is less than Size(Cols)-1, then the valueColumn parameter must be specified to indicate which column contains the cell value.

Before using MDTable, you must define all of the indexes for the result. Each index must include all values that occur in the corresponding column of T or an error will result. The Unique() function is useful for defining the necessary indexes.

Optional parameters:

vars: Must be a list of identifiers of (as text) or handles to Indexes or index names. If an element is a text identifier, it must refer to a global index -- i.e,. not a local index). If it is a handle, it can refer to a local index. If you don't specify optional parameter Vars, MDTable() assumes that the first Size(Col)-1 elements of the Col' index are identifiers of Global indexes to be used in the result. You must make sure that all these Indexes have as values all the unique values from the corresponding column in T, for example:

Index Cols := ['A', 'B', 'Value']
Index Rows := 1..100
Variable T := Table(Cols, Rows)(.....)
Index A := T[Cols = 'A', Rows = Unique(T[Cols = 'A'], Rows)]
Index B := T[Cols = 'B', Rows = Unique(T[Cols = 'B'], Rows)]
Variable Result := MDArray(T, Rows, Cols)

It is possible that two or more rows of T specify identical coordinates. In this case, a conglomeration function is used combine the values for the given cell. The conglomFn parameter is a text value specifying which conglomeration function is to be used. Some possible values are: "sum" (default), "min", "max", "average", or "product". You can also create your own custom conglomeration function.

conglomerationFn: (Default: Sum) Specifies the function used to aggregate over values from the rows in T that map to each cell in the result. Common options are Max, Min, Average, Count. But you can specify the identifier (as text) of any reducing function that operates over an index, with parameters of the form: (A: Array[I]; I: Index). It is OK if it has other parameters as long as they are optional. You can use a user-defined function of this form.

defaultVal: (Default: NULL) The value of a result cell that has no corresponding rows in T. It is often a good idea to set the default to 0.

valueColumn: In OLAP terminology, a fact table is a table in which the first N columns specify coordinates in a multi-dimensional cube, and the last M columns specify measures along a measure dimension, so that the final multi-dimensional array has N+1 dimensions, the extra dimension being the measure dimension. This transformation is accomplished by providing the measure dimension in the valueColumn parameter. The valueColumn parameter should then be a 1-D array, indexed by your measure dimension, where the array elements identify columns in the Cols index. An index with n values equal to the last n values of Cols. In this case, Vars should identify as indexes only the first m-n elements of Cols -- where m=Size(Cols). The remaining Cols are mapped into valueColumn. The result will also be indexed by valueColumn if specified, and each slice in the result contains the values aggregated over the corresponding Col in T. In OLAP terminology, parameter T a "Fact table", which shows more than one value for each combination of Vars.

Library

Array

Examples

Download model with examples (requires Analytica 4.0)

Suppose T, Rows, and Cols are defined as indicated by the following table:

	Cols →
Rows ↓	Car_type	Mpg	X
1	VW	26	2185
2	VW	30	1705
3	Honda	26	2330
4	Honda	35	2210
5	BMW	30	2955
6	BMW	35	2800
7	BMW	35	2870

MDTable(T,Rows,Cols,[Car_type,Mpg],'average','n/a') -->

	Mpg →
Car_type ↓	26	30	35
VW	2185	1705	n/a
Honda	2330	n/a	2210
BMW	n/a	2955	2835

Notice that in the example, Rows 6 and 7 both specified values for Car_type=BMW, Mpg=35. The "average" conglomeration function was used to combine these.

Now suppose we have a User-Defined Function, First:

 Function First( A : Array[I] ; I : Index ) := A[@I=1]

Then

MdTable(T,Rows,Cols,[Car_type,Mpg],'First','n/a') -->

	Mpg →
Car_type ↓	26	30	35
VW	2185	1705	n/a
Honda	2330	n/a	2210
BMW	n/a	2955	2800

To aggregate X over Car_type, we can use the valueColumn. Here we will aggregate using Sum.

MdTable(T,Rows,Cols,[Car_type],valueColumn:'X') →

Car_Type ↓
VW	3890
Honda	4540
BMW	8625

In the previous example, Car_type is the first column. When you aggregate in this fashion, the target aggregation index must be the first column. If you wanted to aggregate onto Mpg (summing all records with the same Mpg) then you would need to re-index first to make Mpg the first column like this:

Index L := ['Mpg','X'];
MdTable(T[Cols=L],Rows,L,[mpg],valueColumn:'X') →

Mpg ↓
26	4515
30	4660
35	7880

If T had 6 coordinate columns and you wanted to aggregate onto 3 dimensions only, then you'd need to make sure that the three final dimensions were in the first three columns. If they were not there initially, then you'd reindex as in the previous example. If you are aggregating only a single target dimension, the Aggregate function can also be used and may be more intuitive. MdTable is actually more general since you can aggregate onto a multi-dimensional table.

To use both Mpg and X as value columns, we can define a measure dimension:

Index Measure_Index := ['Mpg','X']

Then

MdTable(T,Rows,Cols,[Car_type],valueColumn:Measure_Index) →

	MeasureIndex →
Car_Type ↓	Mpg	X
VW	56	3890
Honda	61	4540
BMW	100	8625

Notice here that both Mpg and X have been summed -- both values used the same conglomeration function. However, suppose we want the average value for Mpg, but the maximum value for X, i.e., each "measure" having its own conglomeration. We can accomplish this using:

MdTable(T,Rows,Cols,[Car_type],valueColumn:Measure_Index,
        conglomerationFn: Array(Measure_Index,["average","max"]) ) →

	MeasureIndex →
Car_Type ↓	Mpg	X
VW	28	2185
Honda	30.5	2330
BMW	33.3	2955

Fact Table

In order to convert a 2D relational table with more than one value per combination of indexes, you would use the parameter valueColumn to create a Fact Table. For example, suppose T, Rows, and Cols are defined as indicated by the following table:

	Cols →
Rows ↓	Car_type	Mpg	X	Y
1	VW	26	2185	1
2	VW	30	1705	2
3	Honda	26	2330	3
4	Honda	35	2210	3
5	BMW	30	2955	4
6	BMW	35	2800	5
7	BMW	35	2870	5

And suppose Fact is an index defined as ['X','Y']. Therefore:

MDTable(T,Rows,Cols,[Car_type,Mpg],valueColumn:Fact) -->

MPG = 26	MPG = 30	MPG = 35
Fact → Car_type ↓ X Y VW 2185 1 Honda 2330 3 BMW n/a n/a	Fact → Car_type ↓ X Y VW 1705 2 Honda n/a n/a BMW 1955 4	Fact → Car_type ↓ X Y VW n/a n/a Honda 2210 3 BMW 5670 10

Notice that in the example, Rows 6 and 7 both specified values for Car_type=BMW, Mpg=35. By default the "sum" conglomeration function was used to combine these.

What's new in 4.0

MdTable contains several enhancements that are new to Analytica 4.0:

• The valueColumn parameter. Previously, all Size(Cols)-1 columns has to be used as a coordinate, and the value had to be in the last column.
• Better support for VarTerms in the Vars parameter. This is particular useful when you want the result to use local indexes.
• Support for named parameters, some changes to the names (ConglomFn is now ConglomerationFn, missingVal is now defaultValue).
• Support for custom aggregation functions.
• DefaultValue for missing cells is Null when not specified explicitly (formerly it was Undefined).

See Also

• Unique
• MdArrayToTable