Relational tables and multiD arrays

Analytica User GuideMore Array FunctionsRelational tables and multiD arrays

The MDArrayToTable() function “flattens” a multi-dimensional array into a two-dimensional relational table. When a simple relational transformation is desired, the table will have one row for each array element and only one column (the right-most column) containing those element values. Alternatively MDArrayToTable() can produce a fact table in which values occupy multiple columns divided over a specified index, referred to as the Value Index. Both of these methods are described in separate sections below. The third section applies to both kinds of tables and describes partial transformations in which the MDArrayToTable() function will only operate on a subset of indexes, leaving the rest in array form.

The MDTable() function does the inverse, creating a multi-dimensional array from a table of values. Viewing tabular results in a multi-dimensional form via MDTable() often provides informative new perspective on existing data.

Many external application programs, including spreadsheets and relational databases, are limited to two-dimensional tables. Thus, before transferring multi-dimensional data between these applications and Analytica, it might be necessary to convert between multi-dimensional arrays and two-dimensional tables.

MDArrayToTable(a, row, col) (pure relational transformation)

Transforms a multi-dimensional array, a, into a two-dimensional array (i.e., a table) indexed by row and col. The result contains one row along row for each element of a. Each column along col, except the far right column, represents a coordinate index from the array. Index columns are populated with the coordinate values for the element. The far right column contains the actual value of the element. Both row and col are optional, if either or both is omitted, a local index of the appropriate length is automatically created. The local index col contains handles to the array’s indexes for the coordinate columns.

If you provide the index row with the number of elements equal to size(a), the resulting table will contain all array elements. If the number of elements in row is equal to the number of nonempty elements of a, the resulting table will contain only the non-empty elements of the array. If the number of elements in row is anything else, an error will occur. The optional parameters omitNull and omitZero control what is considered to be empty. When you omit row, the local index is sized for the non-empty elements, or you can obtain all elements by setting both omitNull and omitZero to false.

Library: Array

Example: Starting with “Array_3x3x3” indexed by “Number”, “Letter”, and “Hue”

	Number ▶
Letter ▼	1	2	3
A	45	19	92
B	13	21	81
C	12	43	47

	Number ▶
Letter ▼	1	2	3
A	34	25	45
B	11	62	19
C	84	45	53

	Number ▶
Letter ▼	1	2	3
A	21	65	95
B	48	33	12
C	57	56	23

Row :=	sequence(1, size(Array_3x3x3))
Col :=	['Number',	'Letter',	'Hue',	'Value']
	All but the last column elements specify indexes from the array			The last column element contains the name heading of the value column. It does not specify an index

MDArrayToTable(Array_3x3x3, Row, Col) →

	Col ▶
Row ▼	Number	Letter	Hue	Value
1	1	A	Red	45
2	1	A	Green	34
3	1	A	Blue	21
4	1	B	Red	13
...
26	3	C	Green	53
27	3	C	Blue	23

The resulting table populates Index columns with the corresponding index labels for the array element.

Set the optional parameter positional to true to return the index positions rather than index labels:

MDArrayToTable(Array_3x3x3, Row, Col, positional: true) →

	Col ▶
Row ▼	Number	Letter	Hue	Value
1	1	1	1	45
2	1	1	2	34
3	1	1	3	21
4	1	2	1	13
...
26	3	3	2	53
27	3	3	3	23

Column headings can be customized using a one-dimensional variable to associate headings and indexes:

Index Headings := ['X', 'Y', 'Z', 'Values'] Variable Index2Heading := Table(Headings) ('Number', 'Letter', 'Hue', 'Anything') MDArrayToTable(Array_3x3x3, Row, Index2Heading, positional: true) →

	Col ▶
Row ▼	X	Y	Z	Values
1	1	1	1	45
2	1	1	2	34
3	1	1	3	21
4	1	2	1	13
...
26	3	3	2	53
27	3	3	3	23

MDArrayToTable(a, row, col, valueIndex) (fact table transformation)

The MDArrayToTable() function creates a fact table whenever the optional valueIndex parameter is used. This format allows you to have multiple columns of values divided along one of the original array indexes, the designated valueIndex. The headings of each value column are typically the elements of the value index. In this type of transformation, the value index is not considered to be a positional coordinate of the original array. Instead, the array is interpreted as having a reduced coordinate system spanned only by the remaining indexes, with each coordinate location containing multiple values.

Example: Starting with the same array from the example above, you can specify Hue as the value index. The array would be considered to have only two remaining coordinate dimensions: Number and Letter. This changes the effective size of the array, and therefore the length of the row index:

Rows := 1..Sum(1, Number, Letter) or equivalently: Row := 1..9

The Col parameter includes index columns as before, but the value column now contains the elements of the value index:

Cols :=	Concat(['Number', 'Letter'], Hue)				or equivalently:
Cols :=	['Number',	'Letter',	'Red',	'Green'	'Blue']
	All but the last column elements specify indexes from the array				The last column element contains the name heading of the value column. It does not specify an index

If you omit the Col parameter, MDArrayToTable will create a local index for you named .Col, using the elements of Hue as the last there elements of the local index.

Include Hue as the ValueIndex parameter:

(Array_3x3x3, Rows, Cols, Hue) →

	Cols ▶
Rows ▼	Number	Letter	Red	Green	Blue
1	1	A	45	34	21
2	1	B	13	11	48
3	1	C	12	84	57
4	2	A	19	25	65
...
8	3	B	81	19	12
9	3	C	47	53	23

See Also

Transforming functionsRelational tables and multiD arraysInterpolation functions