View source for Relational tables and multiD arrays

[[Category: Analytica User Guide]]
[[Category: Array Flattening Functions]]
<breadcrumbs>Analytica User Guide > Array functions > {{PAGENAME}}</breadcrumbs>

__TOC__


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.

See also [[MdArrayToTable]]().

'''Library:''' Array

'''Example:''' Starting with “Array_3x3x3” indexed by <code>Number</code>, <code>Letter</code>, and <code>Hue</code>
:{| class="wikitable"
|
{| class="wikitable"
!
! colspan="3" |Number &#9654;
|-
! Letter &#9660;
!1
!2
!3
|-
! A
|45
|19
|92
|-
! B
|13
|21
|81
|-
! C
|12
|43
|47
|}
<code>Hue = 'Red'</code>
|
|
{| class="wikitable"
!
! colspan="3" |Number &#9654;
|-
! Letter &#9660;
!1
!2
!3
|-
! A
|34
|25
|45
|-
! B
|11
|62
|19
|-
! C
|84
|45
|53
|}
<code>Hue = 'Green'</code>
|
|
{| class="wikitable"
!
! colspan="3" |Number &#9654;
|-
! Letter &#9660;
!1
!2
!3
|-
! A
|21
|65
|95
|-
! B
|48
|33
|12
|-
! C
|57
|56
|23
|}
<code>Hue = 'Blue'</code>
|}

:{| class="wikitable"
|<code>Row := </code>
| colspan="4" |<code>sequence(1, size(Array_3x3x3))</code>
|-
|<code>Col :=</code>
|<code>['Number',</code>
|<code>'Letter',</code>
|<code>'Hue',</code>
|<code>'Value']</code>
|-
|
| colspan="3" |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
|}

:<code>MdArrayToTable(Array_3x3x3, Row, Col) &rarr;</code>
:{| class="wikitable"
!
! colspan="4" |Col &#9654;
|-
!Row &#9660;
!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 <code>true</code> to return the index positions rather than index labels:

:<code>MdArrayToTable(Array_3x3x3, Row, Col, positional: true) &rarr;</code>
:{| class="wikitable"
!
! colspan="4" |Col &#9654;
|-
!Row &#9660;
!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:

:<code>Index Headings := ['X', 'Y', 'Z', 'Values']</code>
:<code>Variable Index2Heading := Table(Headings) ('Number', 'Letter', 'Hue', 'Anything')</code>
:<code>MdArrayToTable(Array_3x3x3, Row, Index2Heading, positional: true) &rarr;</code>
:{| class="wikitable"
!
! colspan="4" |Col &#9654;
|-
!Row &#9660;
!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: 

:<code>Rows := 1..Sum(1, Number, Letter)</code> or equivalently: <code>Row := 1 .. 9</code>

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

:{| class="wikitable"
|<code>Cols := </code>
| colspan="4" |<code>Concat(['Number', 'Letter'], Hue)</code> 
| or equivalently:
|-
|<code>Cols :=</code>
|colspan="2" |<code>['Number', 'Letter',</code>
|colspan="3" |<code>'Red', 'Green', 'Blue']</code>
|-
|
| colspan="2" |First column elements specify 
remaining coordinate indexes in the array.
|colspan="3"| The right-most ''n'' column elements 
are elements of the value index, 
where ''n'' is the size 
of the value index.
(In this example '''Hue''' has three elements)
|}

If you omit the «col» parameter, [[MdArrayToTable]]() will create a local index for you named <code>.Col</code>, using the elements of <code>Hue</code> as the last there elements of the local index.

Include '''Hue''' as the «valueIndex» parameter:

:<code>(Array_3x3x3, Rows, Cols, Hue) &rarr;</code>
:{| class="wikitable"
!
! colspan="5" |Cols &#9654;
|-
!Rows &#9660;
!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
|}

==MdArrayToTable() (partial transformation)==
In both examples above, the [[MdArrayToTable]]() function completely “flattened” the entire array into a two-dimensional table. But every array can be regarded as a stack of smaller sub-arrays, each having one less dimension than the whole. If [[MdArrayToTable]]() operates on only one of these sub-arrays, the result will also be a flat two-dimensional table. If it operates on each of the sub-arrays individually, the result will be a stack of tables which can no longer be described as completely “flat”. Extending this idea, it is possible to imagine a plane of tables or a cube of tables if the original array starts off with enough dimensions. This essentially describes the idea of a partial transformation. The [[MdArrayToTable]]() function is not obliged to operate on all available array indexes. In fact there are often advantages to leaving some dimensions intact since they can still be analyzed using Analytica’s array abstraction features.

Specifying which dimensions are to be preserved in an [[MdArrayToTable]]() transformation involves a specialized coding technique described by some advanced users in New Jersey as, “fugetaboutit!” If you simply forget about certain indexes and remove all references to them in the [[MdArrayToTable]]() function and its parameters, the result will be an array of tables with dimensions along the omitted indexes. This is true whether or not the «valueIndex» parameter is used. In any case the length of the «row» parameter must be equal to the number of elements (or mutually non-empty elements) in each of the flattened sub-arrays.

==MdTable(T, rows, cols, ''vars, conglomerationFn, defaultValue, valueColumn'')==
Returns a multi-dimensional array from a two-dimensional table of values. If ''n'' is the total number of columns and ''m'' is the number of value columns in the table «T», the following structure is assumed: The first ''n-m'' columns of «T» specify coordinates, and the right-most m columns contain data values. When there is only one data value in each row (a single value column) the table is described as a pure relational table. When there are multiple value columns it is described as a fact table.

«rows» and «cols» specify the vertical and horizontal indexes of the two-dimensional table. The length of the rows index is equal to the number of data records in the table. The length of cols is equal to the total number of columns.

The optional parameter «valueColumn» specifies the index over which multiple value columns are divided in a fact table. If «valueColumn» is omitted, the table is assumed in pure relational format, having a single value column.

The parameter «vars» is a list of index identifiers specifying the coordinate dimensions of the final array. It is optional if the table is in ''pure'' relational format (single value column). If «vars» is omitted, the dimensions of the final result are specified by the first ''n-1'' elements of «cols», where ''n'' is the number of elements in cols.

If a ''fact table'' (multiple value columns) is being transformed then you must use «vars» to specify the coordinate indexes of the array. The elements of «vars» must correspond to the coordinate columns of the table or an error will result. The number of elements in «vars» should be equal to ''n-m'' where ''n'' is the number of elements in cols and ''m'' is the number of value columns. Note that the list of coordinate indexes does not include the ''value index''.

It is possible that two or more rows of «T» specify identical coordinates. In this case, a ''conglomeration function'' is used to combine the values for the given cell. The «conglomerationFn» parameter is a text value specifying which conglomeration function is to be used. Possible values are <code>"sum"</code> (default), <code>"min", "max", "average"</code>, and ,<code>"product"</code>.

It is also possible that no row in «T» corresponds to a particular cell. In this case, the cell value is set to «defaultValu»', or if the «defaultValue» parameter is omitted, the cell value is set to ''undefined''. Undefined values can be detected using the [[IsUndef]]() function.

See also [[MdTable]]().

'''Library:''' Array

'''Example:''' Starting with the fact table produced in the previous section:
:{| class="wikitable"
!
! colspan="5" |Cols &#9654;
|-
!Rows &#9660;
!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
|}

:<code>Index rows := 1..9</code>
:<code> Index cols := ['Number', 'Letter', 'Red', 'Green', 'Blue']</code>
:<code> Index Hue := ['Red', 'Green', 'Blue']</code>
:<code>MDTable(T, rows, cols, ['Number', 'Letter'], ValueColumn: Hue) &rarr;</code>
:{| class="wikitable"
|
{| class="wikitable"
!
! colspan="3" |Number &#9654;
|-
! Letter &#9660;
!1
!2
!3
|-
! A
|45
|19
|92
|-
! B
|13
|21
|81
|-
! C
|12
|43
|47
|}
<code>Hue = 'Red'</code>
|
{| class="wikitable"
!
! colspan="3" |Number &#9654;
|-
! Letter &#9660;
!1
!2
!3
|-
! A
|34
|25
|45
|-
! B
|11
|62
|19
|-
! C
|84
|45
|53
|}
<code>Hue = 'Green'</code>
|
{| class="wikitable"
!
! colspan="3" |Number &#9654;
|-
! Letter &#9660;
!1
!2
!3
|-
! A
|21
|65
|95
|-
! B
|48
|33
|12
|-
! C
|57
|56
|23
|}
<code>Hue = 'Blue'</code>
|}

'''Example:''' Modifying the table by changing the (1, C) coordinate to (1, B), it now contains a missing value at (1, C) and redundant values at (1, B):

:{| class="wikitable"
!
! colspan="5" |Cols &#9654;
|-
!Rows &#9660;
!Number
!Letter
!Red
!Green
!Blue
|-
!1
|1
|A
|45
|34
|21
|-
!2
|1
|B
|13
|11
|48
|-
!3
|1
|B
|12
|84
|57
|-
!4
|2
|A
|19
|25
|65
|-
!...
|
|
|
|
|
|-
!8
|3
|B
|81
|19
|12
|-
!9
|3
|C
|47
|53
|23
|}

:<code>MDTable(T, rows, cols, ['Number', 'Letter'], 'average', 'N/A', Hue) &rarr;</code>
:{| 
{| class="wikitable"
|
{| class="wikitable"
!
! colspan="3" |Number &#9654;
|-
! Letter &#9660;
!1
!2
!3
|-
! A
|45
|19
|92
|-
! B
|12.5
|21
|81
|-
! C
|N/A
|43
|47
|}
<code>Hue = 'Red'</code>
|
|
{| class="wikitable"
!
! colspan="3" |Number &#9654;
|-
! Letter &#9660;
!1
!2
!3
|-
! A
|34
|25
|45
|-
! B
|47.5
|62
|19
|-
! C
|N/A
|45
|53
|}
<code>Hue = 'Green'</code>
|
|
{| class="wikitable"
!
! colspan="3" |Number &#9654;
|-
! Letter &#9660;
!1
!2
!3
|-
! A
|34
|25
|45
|-
! B
|47.5
|62
|19
|-
! C
|N/A
|45
|53
|}
<code>Hue = 'Blue'</code>
|}

==MdTable() (partial transformation)==
If the input is a partially flattened array of tables, no special action is necessary. The pre-existing dimensions will automatically be rolled up into the final array without any reference being made to them. This is consistent with the general principles of array abstraction in Analytica.

==See Also==
* [[MdTable]]
* [[MdArrayToTable]]
* [[Array-reducing functions]]
* [[Convert an Analytica multidimensional array into an Excel PivotTable]]


<footer>Transforming functions / {{PAGENAME}} / Interpolation functions</footer>