MdArrayToTable(A,I,L)

Transforms a multi-dimensional array, A, into a two-dimensional array (i.e., a relational table) indexed by I and L. The result contains one row along I for each element of A. L must contain a list of names of the indexes of A, followed by one final element. All elements of L must be text values. The column corresponding to the final element of L contains the cell value. If L does not contain all the indexes of A, array abstraction will create a set of tables indexed by the dimensions not listed in L.

Creating Sparse Relational Tables

You can elect to include only the non-null / non-zero elements of «A» in the resulting table. Normally, when you want all elements, you provide an index «I» with a length equal to the total number of cells in the array. To include only the non-zero / non-null elements, provide an index with a length equal to the number of non-null / non-zero elements.

New to Analytica 4.2: Prior to 4.2, only zero elements were excluded. The ability to exclude Null elements is new. The «omitZero» and «omitNull» parameters are new to 4.2.

If you want to include zeroes, but not Nulls, then size «I» for the number of non-Null values and specify:

MdArrayToTable(...,omitZero:false)

Likewise, if you want to include Null cells as rows, but not zeros, specify:

MdArrayToTable(...,omitNull:false)

Creating a fact table

New to Analytica 4.2

MdArrayToTable(A,I,L,valueIndex)

A fact table is a generalization of a simple relational table. In a fact table, the first N columns identify cell coordinates, and the last K columns hold measured values (often called measures, facts, or just values). This generalized the simple relational table where only the single final row holds a value.

The optional parameter, «valueColumn», specifies an index of «A» that is used for the value columns in the result. The first Size(L) - Size(valueIndex) columns of «L» are then the coordinates, and the final Size(valueIndex]] columns of «L» hold the values.

When omitting cells having no data, all values in «A» along the valueIndex must be zero or null. If any of the values have data, the corresponding cell (i.e., row in the result) is considered to have data.

Positional Coordinates

New to Analytica 4.2

MdArrayToTable(...,positional:true)

Normally, the first N columns of the result specify coordinates using index labels. When the optional parameter «positional» is specified as true, index positions are returned rather than labels.

Library

Array

Examples

Define these global objects (as indexes or variables):

Rows := sequence(1,size(Cost_in_time))
Cols := [‘Mpg’,’Time’,’Car_type’,’Cost’]

MDArrayToTable(Cost_in_time,Rows,Cols) →

Rows v, Cols >>
	Mpg	Time	Car_type	Cost
1	26	0	VW	2185
2	26	0	Honda	2385
3	26	0	BMW	3185
4	26	1	VW	2294
5	26	1	Honda	2314
6	26	1	BMW	3294
7	26	2	VW	2409
...
45	35	4	BMW	5175

Note: The expression for doing the same transformation using local indexes looks like

Index Rows := Sequence(1,Size(Cost_in_time))
Index Cols := [‘Mpg’,’Time’,’Car_type’,’Cost’]
MdArrayToTable(Cost_in_time,Rows,Cols)

Positional example

Changing the previous example to:

MdArrayToTable(Cost_in_time,Rows,Cols,positional:true)

causes the coordinates in the first 3 columns to be expressed as index positions, rather than index labels.

Rows v, Cols >>
	Mpg	Time	Car_type	Cost
1	1	1	1	2185
2	1	1	2	2385
3	1	1	3	3185
4	1	2	1	2294
5	1	2	2	2314
6	1	2	3	3294
7	1	3	1	2409
...
45	3	5	3	5175

Creating a Fact Table

In this example, well provide a separate column for each car-type, rather than including that as a relational dimension. The numbers in the columns are the cost.

Index Row := 1..Sum(1,Mpg,Time)
Index Label := [Mpg,Time,'VW','Honda','BMW'];
Variable Cost := MdArrayToTable(Cost_in_time,Row,Label,valueIndex:Car_type)

Cost →

Rows v, Cols >>
	Mpg	Time	VW	Honda	BMW
1	26	0	2185	2385	3185
2	26	1	2294	2314	3294
3	26	2	2409	2512	3359
...
15	35	4	3829	4230	5175

MdArrayToTable

Contents