Data Arrays

An array is a numbered collection of data items. Each item contains a value. Items in an array are always numbered sequentially starting with 1 – item 1, 2, 3, etc. Items are always accessed by their number – for example item #3, or items #4 thru #7 (a range). You can add new items to the end of an array, insert items in the middle, change an item’s value or delete an item completely.

Although an array contains a collection of items, it is also an entity itself. You can easily make a copy of an array in a different variable, or store it in a database cell.

#### Data Arrays vs. Text Arrays

Panorama has two types of arrays, Data Arrays and Text Arrays. Both are numbered collections of items. The difference is in how the collection items are stored.

Data Arrays use a special binary format to store the items that are contained in the array. Panorama keeps track of all of the details – if you ask it to retrieve item #12 from the array, Panorama knows where that is and gets it for you. You don’t have to worry about what type of data it is or where it is stored. Panorama knows exactly what is in the array and how to access and modify it. You can’t access the items in a data array directly, a data array looks like gibberish (for example `ÕÃÃ4@ffffff$@33333@öôô@`) if you try to look at it directly. You must use special functions and statements to access or modify the values in a data array.

Text Arrays don’t use a special format – they are simply text that contains items separated by a special character, or characters, for example a comma or carriage return. Here is an example of a comma separated (also called comma delimited) text array. This text array contains five items.

    john,mary,william,mark,susan

As with Data Arrays, Panorama has special functions and statements for accessing and modifying text arrays (in fact, in most cases they are the same functions and statements for both types of arrays). However, in the case of text arrays they can also be displayed and edited simply as text.

So which should you use – data arrays or text arrays? There is no one fixed answer. However, in general, we find ourselves using text arrays more frequently because of these advantages:

- Text arrays are super easy to create and use.
- Text arrays are transparent – you can easily see and edit the contents.
- Text arrays can be manipulated with standard text tools.

Text arrays do have some potential disadvantages, though:

- Values in a text array must be text (no numbers or binary values).
- When using a text array, you must pick and remember the separator character(s).
- Values in a text array cannot contain the separator character. For example, if the array is comma separated, values in the array cannot contain commas.
- When using a text array, it is impossible to tell the difference between an empty array and an array with one empty value.

To learn more about text arrays, see Text Arrays.

#### Arrays vs. Dictionaries

Panorama has two types of collections – arrays and dictionaries. As described above, an array( is a numbered collection of data items. A dictionary is an unordered collection, where each item is identified by a name instead of a number. To learn more about dictionaries, see Data Dictionaries.

#### Data Array Storage

Panorama keeps Data Arrays in a special binary format. If you are keeping a data array in a variable, this doesn’t matter – Panorama takes care of everything for you. But if you want to store a data array in a database field, the field must be a Binary field (see Binary Data).

#### Creating a Data Array

The dataarray( function creates a data array from one or more separate values:

    local myarray
    myarray = dataarray("Bob",123,3.14159,"Green")

Here is an example of a data array which contains another data array (it could also contain a data dictionary if you wished):

    dataarray(358,"blue",909,dataarray(6,12,7,5),"SFO")

The generatedataarray( function creates a data array containing a specific number of elements, each of which is based on a formula. It has two parameters, the number of elements in the array and the formula used to generate each element. This formula can use the seq( function if the values are to be based on the element number. The entire formula must be enclosed in quotes. This example creates an array with ten text values: Price1, Price2, Price3, etc.

    generatedataarray(10,{"Price"+seq()})

#### Accessing an Individual Array Item

The array( function will extract a single item from an array. When used with a data array this function has two parameters – the array itself, and the item number. This formula accesses the seventh name from an array of names.

    array(Names,7)

What happens if you use the array( function to access an array value that doesn’t exist? It depends on what’s in the array. If all values are text, the function returns empty text:

    array(dataarray("gold","silver","bronze","lead"),7) ☞

If all values are the same type of number, the function returns zero:

    array(dataarray(23,567,109,4),7) ☞ 0

If the array is all binary values, the function returns an empty binary value (a binary data item of zero length).

If the array values are a mix of types, the function returns an error message, even if you mix just integer and floating point numbers:

    array(dataarray("gold",3.1416,"bronze","lead"),7) ☞ Undefined array value

In this last case, you could use the catcherror( function to provide a default value for missing elements.

    catcherror(0,array(dataarray("gold",3.1416,"bronze","lead"),7)) ☞ 0

##### Panorama Arrays vs. C, JavaScript, etc.

If you’ve used other programming languages, you may be used to a bracket notation for accessing array items, for example:

    Names[7]

Panorama doesn’t support this sort of bracket notation. Instead, you must use the array( function as described in the previous section.

#### Functions that work with both Data Arrays and Text Arrays

The array( function described in the previous section also works with Text Arrays, but in that case a third parameter is required to specify the separator character. If the Names variable contained comma separated text instead of a data array, this is the formula that would be used to access the seventh name.

    array(Names,7,",")

There are a dozen “two way” array functions that work with both data arrays and text arrays. For each of these functions, simply leave off the separator parameter when using the function with a data array.

- array(
- arraychange(
- arraycontains(
- arraynotcontains(
- arraydelete(
- arrayfilter(
- arrayrange(
- arrayrelocate(
- arrayreverse(
- arraysearch(
- arraysize(
- arraystrip(

#### Converting a Data Array to a Text Array

The exportdataarray( function facilitates converting a data array into text. This function is especially convenient for debugging since it allows you to easily convert the data array into a format that can be displayed and examined. This function has two parameters – the data array, and the separator character (or characters) to be used in the output. Here is an example that formats the data array items with a space between them.

    exportdataarray(dataarray(8,2,1,3)," ") ☞ 8 2 1 3

As shown in this example, the exportdataarray( function converts all numeric values to text, using Panorama’s default numeric formats. If you want to control the formats yourself, you can explicitly convert each data array element to text using the arrayfilter( and/or pattern( functions, then use the exportdataarray( function on the result.

    exportdataarray(arrayfilter(dataarray(8,2,6),{pattern(import(),"#.##")}),"+") ☞ 8.00+2.00+6.00

    exportdataarray(generatedataarray(12, |||datepattern(datevalue(2015,seq(),1),"Month")|||),", ")
    ☞ January, February, March, April, May, June, July, August, September, October, November, December

Since it is possible for data arrays to contain values that are not possible in a text array, the conversion of a data array to a text array may not always produce the result you were expecting. If data values in the array contain the separator character, the text array will have a different number of items than the original data array. Also, any binary elements will come across as apparent garbage.

#### Converting a Text Array to a Data Array

The importdataarray( function converts a text array into a data array. This is straightforward if all of the array elements contain text. In fact, even if the text array elements appear to contain numbers, this function will import them as text, as shown in this example.

    local array1
    array1 = "2,7,18,5,360"
    importdataarray(array1,",") ☞ "2","7","18","5","360"

Although the elements of the text array are all numbers, they have been stored as text and so the elements of the data array will all be text values, not numbers. If you want the data array to contain numeric values, you need to add a third parameter, which specifies what type of numbers to use. Of course this will only work if all of the values are legal numbers – otherwise you will wind up with zeros.

    importdataarray("2,0.7,18,5.12,3.605",",","integer") ☞ 2, 0, 18, 5, 3
    importdataarray("2,0.7,18,5.12,3.605",",","float") ☞ 2, 0.7, 18, 5.12, 3.605

Note: The commas shown in the results above don’t actually exist in the resulting data array, they are simply shown for illustration purposes.

#### Converting Database Information into a Data Array

The dataarraybuild( function builds a data array by scanning a database and creating an array element for every record (including invisible records) in the database. Its format is:

    dataarraybuild(Database, Formula, Query)

The Database parameter is the database to be scanned, Formula is the formula that will be used to extract data from the database and build each array element. The entire formula must be enclosed in quotes. Query is an optional formula that determines whether a record should be included in the output array.

Be aware that, when the arraybuild( function is used to build a text array, it ignores values for which the formula is an empty string. In contrast, the dataarraybuild( function will include those values in a data array.

The dataarrayselectedbuild( function works in exactly the same way as the dataarraybuild( function except that it scans only the visible records in the database.

#### Adding New Elements to a Data Array

You can add an element to a text array with an expression like this:

    textArray = textArray + Separator + newElement

but you cannot do that with a data array. You must use the dataarrayappend( function (not to be confused with the appenddataarrays( function which joins two or more data arrays to one another) to add one or more elements. Suppose you have created a data array like this:

    local myarray
    myarray = dataarray("red","blue","orange")

You can use dataarrayappend( to add as many colors as you like to the end of this array.

    myarray = dataarrayappend(myarray,"green","silver")

The result is a data array with five elements: red, blue, orange, green and silver.

You can insert multiple items at the beginning or in the middle of an array with the dataarrayinsert( function. This example inserts Drone, Bicycle, and Rickshaw between UPS and Post Office (position 3):

    dataarrayinsert(dataarray("Fedex","UPS","Post Office"),3,"Drone","Bicycle","Rickshaw")

#### Appending Data Arrays

The appenddataarrays( function (not to be confused with the dataarrayappend( function which adds values to a data array) joins two or more data arrays end to end. Suppose you have created three data arrays like this:

    local primaries,pastels,grays
    primaries=dataarray("red","green","blue")
    pastels=dataarray("pink","mauve","baby blue")
    grays=dataarray("black","white")

You can use appenddataarrays( to combine all of these into a new data array.

    local colors
    colors = appenddataarays(primaries,pastels,grays)

The end result is a data array with eight elements: red, green, blue, pink, mauve, baby blue, black and white.

#### Rearranging the Elements of a Data Array

This section covers the use of five text array functions which allow for the omission of the separator if the target array is a data array.

- arraychange(
- arraydelete(
- arrayrelocate(
- arrayreverse(
- arraystrip(

All of these functions rearrange the array elements. In the examples, commas have been used to separate the elements of the output data arrays. This has been done for clarity of display – remember that data arrays have no separators.

In this example, the arraychange( function is used to change the fourth element of the array to 8:

    arraychange(dataarray(3,"hello",49,43,7.21,"blue"),8,4) ☞ 3,hello,49,8,7.21,blue
    arraychange(dataarray(3,"hello",49,43,7.21,"blue"),8,7) ☞ arraychange( cannot change values outside its array bounds

Suppose we have a data array, myDataArray, defined as below:

    myDataArray = dataarray("Bob",123,3.14159,"Green",303)

The arraydelete( function can be used to delete one or more elements from this array.

    arraydelete(myDataArray,2,1) ☞ Bob,3.14159,Green,303
    arraydelete(myDataArray,3,2) ☞ Bob,123,303
    arraydelete(myDataArray,7) ☞ arraydelete( cannot delete outside of array bounds

Here, the arrayrelocate( function has moved the fifth element of the array to position 1:

    arrayrelocate(dataarray(3,"hello",49,43,7.21,"blue"),5,1) ☞ 7.21,3,hello,49,43,blue
    arrayrelocate(dataarray(3,"hello",49,43,7.21,"blue"),5,8) ☞ New item position is beyond end of array

The arrayreverse( function does just what its name implies:

    arrayreverse(dataarray(3,"hello",49,43,7.21,"blue")) ☞ blue,7.21,43,49,hello,3

The arraystrip( function operates on a data array a little differently to the way it does on a text array in that it strips out empty text, empty binary values and zero numeric (integer or floating point) values.

    exportdataarray(arraystrip(dataarray(3, "", 9, 0, 25, 0.00)),";") ☞ 3;9;25

#### Extracting an Array Subset

The arrayrange( function creates a new array from a portion of an existing array.

    arrayrange(dataarray("Bob",123,rectangle(2,4,6,8),3.14159,"Green"),3,5)

will create a new data array containing the third, fourth and fifth elements, viz. rectangle(2,4,6,8), 3.14159, and Green.

If you specify a second item position less than the first item position, the result will be an empty array.

If you specify a first item position beyond the range of the array and a second item position greater than the first item position, you will get the error message, “Undefined array value”.

#### Filtering an Array

The arrayfilter( function creates a new array by applying a formula to each of the elements in an existing array. In this example, the arrayfilter( function is used to access a data array containing five integers and multiply each of the numbers by 10.

    arrayfilter(dataarray(3, 12, 9, 7, 25),{import()*10})

The result will be a new data array containing 30, 120, 90, 70 and 250.

#### Handling Unknown Array Item Types

If you’ve built an array yourself, you probably know what type of data each item is (text, numbers, binary data, etc.) However, sometimes that isn’t the case – perhaps the data has been extracted from some external source, or at least a source that is unknown to the code you are writing now.

If you wish to process an item in some way, it is essential that you know its data type. If you don’t know it intrinsically, Panorama can determine the type for you with the datatype( or expressiontype( functions. Suppose you have a data array, dArray, containing mixed data types and you extract the Nth element:

    local dArray, dElement, N, dType
    dArray = dataarray("Bob",123,rectangle(2,4,6,8),3.14159,"Green")
    N = the result of some calculation
    dElement = array(dArray,N)

The manner in which you process the element may depend on its data type, in which case you might have a block of code something like this:

    dType = datatype(dElement)
    case dType = "Text"
        Process a text item
    case dType = "Integer"
        Process an integer item
    case dType = "Float"
        Process a floating point item
    case dType = "Binary"
        Process a binary item
    endcase

Alternatively, you could use the expressiontype( function to decide whether to extract the item in the first place, as in:

    if expressiontype(array(dArray,N)) = "Text"
        Extract the element and process it.
    endif

The expressiontype( function can also be useful within an arrayfilter( function.

####Interrogating a Data Array#

This section covers the use of four text array functions which allow for the omission of the separator if the target array is a data array. They are the arraycontains(, arraynotcontains(, arraysearch( and arraysize( functions which interrogate an array.

The arraycontains( and arraynotcontains( functions operate just as they do for text arrays:

    arraycontains(dataarray(3,"hello",49,43,7.21,"blue"),5) ☞ 0 (false)
    arraynotcontains(dataarray(3,"hello",49,43,7.21,"blue"),5) ☞ -1 (true)

When used with a text array, the arraysearch( function has four parameters. With data arrays, it has only three and, if the start position for the search is the first element, that parameter can also be omitted, as in:

    arraysearch(dataarray(3.94, 12.1, "blue", 801.9, 47.50), 801.9)  ☞ 4

Wildcards are allowed in a text array search but not in a data array search. If a search fails, zero is returned.

The arraysize( function works much as it does for text arrays except that the size of an empty data array is reported as zero (the function assumes an empty text array contains one empty element and reports a size of one).

    arraysize(dataarray(2,0.7,18,5.12,360)) ☞ 5
    arraysize(dataarray()) ☞ 0

#### Looping Through a Data Array

The loopdataarray statement facilitates the process of looping through the elements of a data array:

    local myarray, aElement, elementNumber
    myarray = dataarray("Bob",123,3.14159,"Green")
    loopdataarray myarray, aElement, elementNumber
        message elementNumber+":  " + aElement
    endloop

will produce a series of messages,

    1:  Bob
    2:  123
    3:  3.14159
    4:  Green

If the data array is empty, the code inside the loop is skipped.

See Also

History

Version | Status | Notes
10.0 | New | New in this version.