Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r0642

D toc

An array is a bracket set of comma-delimited values. The following are valid arrays:

Code Block
[1,2,3]
["A","B"]
["C",["D","E"],"F",["G",["H","I"]]]

Ragged arrays: If the number of elements varies between two arrays, they are considered ragged. In the above, all three arrays have a different number of top-level elements (3,2,4).

Nested arrays: When an array element is an array itself, the element is considered a nested array. See the last example above.

Source Arrays

To be recognized as an array, a source column must contain values that are:

  • Bracketed by square brackets
  • Values in cell are delimited by commas

Such columns are likely to be recognized as Array data type.

For more information, see Array Data Type

Create Arrays

Within 

D s product
rtrue
, you can generate arrays using values from one or more columns to do so.

Create by extraction

You can create an array of values by extracting pattern-based values from a specified column. The following transformation extracts from the msg column a list of all values where all letters are capitalized and places them into the new acronyms column:

D trans
p03Valueacronyms
Typeref
p01NameColumn
p01Valuemsg
p02NamePattern matching elements in the list
p02Value`{upper}+`
p03NameNew column name
SearchTermExtract matches into Array

msgacronyms
SCUBA, IMHO, is the greatest sport in the world.["SCUBA","IMHO"]
 []
LOL, that assignment you finished is DOA. You need to fix it PDQ.["LOL","DOA","Y","PDQ"]

Notes:

  • An empty input column value renders an empty array.
  • In the final row, the 

    D s item
    itempattern
     matches on the "Y" value. To fix this, you can change the Pattern matching value to the following, which matches on two or more uppercase letters in a row:

    Code Block
    `{upper}{upper}+`

Create by nesting

You can create arrays by nesting together the values from multiple columns.

Source:

num1num2num3
111213
141516
171819

You want to nest the values in num1 and num2 into a single array and then to nest the array with num3:

Info

NOTE: If you are nesting a multi-level array, you should nest from the lowest level to the top level.

D trans
p03ValueArray
Typeref
p01NameColumns1
p01Valuenum1
p02NameColumns2
p02Valuenum2
p03NameNest columns to
p04Valuenest1
p04NameNew column name
SearchTermNest columns into Objects

Then, you can perform the nesting of the top-level elements:

Info

NOTE: The order in which you list the columns to nest determines the order in which the elements appear in the generated array.

D trans
p03ValueArray
Typeref
p01NameColumns1
p01Valuenest1
p02NameColumns2
p02Valuenum3
p03NameNest columns to
p04Valuenest2
p04NameNew column name
SearchTermNest columns into Objects

In the generated columns, you notice that all values are quoted, even though these values are integers.

Info

NOTE: Elements that are generated into arrays using a nest transformation are always rendered as quoted values.

You can use the following transformation to remove the quotes from the nest2 column:

D trans
p03Value(empty)
Typeref
p01NameColumn
p01Valuenest2
p02NameFind
p02Value'"'
p03NameReplace
p04Valuetrue
p04NameMatch all occurrences
SearchTermReplace text or patterns

After removing the unused nest1 column, the data looks like the following:

num1num2num3nest2
111213[[11,12],13]
141516[[14,15],16]
171819[[17,18],19]

Create from column values

You can use one of several available functions to create arrays from a column's values. 

Source:

listVals
5
TRUE
{"key1":"value1","keys2":"value2"}
[1,2,3]
My String
-5.5

The following transformation generates a new column in which each row contains an array of all of the values of the input column:

D trans
p03ValuelistOfListVals
Typeref
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueLIST(listVals,1000)
p03NameNew column name
SearchTermNew formula

Results:

listValslistOfListVals
5["5","TRUE","{\"key1\":\"value1\",\"keys2\":\"value2\"}","[1,2,3]","My String","-5.5"]
TRUE["5","TRUE","{\"key1\":\"value1\",\"keys2\":\"value2\"}","[1,2,3]","My String","-5.5"]
{"key1":"value1","keys2":"value2"}["5","TRUE","{\"key1\":\"value1\",\"keys2\":\"value2\"}","[1,2,3]","My String","-5.5"]
[1,2,3]["5","TRUE","{\"key1\":\"value1\",\"keys2\":\"value2\"}","[1,2,3]","My String","-5.5"]
My String["5","TRUE","{\"key1\":\"value1\",\"keys2\":\"value2\"}","[1,2,3]","My String","-5.5"]
-5.5["5","TRUE","{\"key1\":\"value1\",\"keys2\":\"value2\"}","[1,2,3]","My String","-5.5"]


Notes:

  • The second parameter on the LIST function defines the maximum number of values to write. 1000 is the default.
  • All values in the generated array are written as String values.
  • Quoted values are escaped in the output.

The following functions allow you to generate various types of arrays from a column's set of values.

FunctionDescription
LIST Function

D excerpt include
pageLIST Function
nopaneltrue

UNIQUE Function

D excerpt include
pageUNIQUE Function
nopaneltrue

LISTIF Function

D excerpt include
pageLISTIF Function
nopaneltrue

ROLLINGLIST Function

D excerpt include
pageROLLINGLIST Function
nopaneltrue

RANGE Function

D excerpt include
pageRANGE Function
nopaneltrue

Info

NOTE: The lower bound of the range is included, while the upper bound is not.

Tip

Tip: Additional examples are available in the above links for these functions.

Create from Object type

You can extract the keys of an Object column into an array of string values. In an Object type, the values are listed in quoted key/value pairs and can be nested. See Object Data Type.

Source:

Suppose your Object data looks like the following:

myObject
{"key1":"value1","key2":"value2","key3":"value3"}
{"apples":"2","oranges":"4"}
{"planes":{"boeing":"5","airbus":"4"},"trains":{"amtrak":"1","SP":"2"}, "automobiles":{"toyota":"100","nissan":"50"}}

You can run the following transformation to extract the top-level keys into arrays in a new named column:

Info

NOTE: The KEYS function retrieves only the top-level keys from the Object.

D trans
p03ValuemyObjectKeys
Typeref
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueKEYS(myObject)
p03NameNew column name
SearchTermNew formula

Results:

myObjectmyObjectKeys
{"key1":"value1","key2":"value2","key3":"value3"}["key1","key2","key3"]
{"apples":"2","oranges":"4"}["apples","oranges"]
{"planes":{"boeing":"5","airbus":"4"},"trains":{"amtrak":"1","SP":"2"}, "automobiles":{"toyota":"100","nissan":"50"}}["planes","trains","automobiles"]

For more information, see KEYS Function.

Read from Arrays

You can read values from arrays in your dataset. 

Info

NOTE: After an array has been created, you can append to the array or otherwise combine it with another array. You cannot replace values in the array without breaking apart the array and rebuilding it.

FunctionDescription
IN Function

D excerpt include
pageIN Function
nopaneltrue

ARRAYELEMENTAT Function

D excerpt include
pageARRAYELEMENTAT Function
nopaneltrue

ARRAYLEN Function

D excerpt include
pageARRAYLEN Function
nopaneltrue

ARRAYUNIQUE Function

D excerpt include
pageARRAYUNIQUE Function
nopaneltrue

Tip

Tip: Additional examples are available in the above links for these functions.

Compute from Arrays

You can use the following functions to perform computations on the values in your arrays:

 

FunctionDescription
LISTSUM Function

D excerpt include
pageLISTSUM Function
nopaneltrue

LISTMAX Function

D excerpt include
pageLISTMAX Function
nopaneltrue

LISTMIN Function

D excerpt include
pageLISTMIN Function
nopaneltrue

LISTAVERAGE Function

D excerpt include
pageLISTAVERAGE Function
nopaneltrue

LISTVAR Function

D excerpt include
pageLISTVAR Function
nopaneltrue

LISTSTDEV Function

D excerpt include
pageLISTSTDEV Function
nopaneltrue

LISTMODE Function

D excerpt include
pageLISTMODE Function
nopaneltrue

Combine Arrays

You can combine arrays together using a variety of methods of combining.

Source:

array1array2
["1","2","3"]["A","B","C"]
["4","5","6"]["D","E","F"]
["7","8","9"]["G","H","I"]

The following transformation concatenates the above arrays into a single single array:

D trans
p03ValuearrayConcat
Typeref
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueARRAYCONCAT([array1,array2])
p03NameNew column name
SearchTermNew formula

Results:

array1array2arrayConcat
["1","2","3"]["A","B","C"]["1","2","3","A","B","C"]
["4","5","6"]["D","E","F"]["4","5","6","D","E","F"]
["7","8","9"]["G","H","I"]["7","8","9","G","H","I"]

These functions can be used to combine arrays together:

FunctionDescription
ARRAYCONCAT Function

D excerpt include
pageARRAYCONCAT Function
nopaneltrue

ARRAYCROSS Function

D excerpt include
pageARRAYCROSS Function
nopaneltrue

ARRAYINTERSECT Function

D excerpt include
pageARRAYINTERSECT Function
nopaneltrue

ARRAYSTOMAP Function

D excerpt include
pageARRAYSTOMAP Function
nopaneltrue

ARRAYZIP Function

D excerpt include
pageARRAYZIP Function
nopaneltrue

Tip

Tip: Additional examples are available in the above links for these functions.

 

Break out Arrays

Expand arrays into rows

You can break out arrays into individual values using the following transformations. Here is some example data from the nest2 column that was generated earlier. The num3 column is retained for reference:

num3nest2
13[[11,12],13]
16[[14,15],16]
19[[17,18],19]

You can use the following simple transformation to flatten the values in nest2 into individual values in each row:

Info

NOTE: Depending on the number of elements in your arrays, you can significantly increase the size of your dataset.

Info

NOTE: If a cell in the source column does not contain an array, an empty value is written into the corresponding row.

D trans
Typeref
p01Namecolumn
p01Valuenest2
SearchTermConvert Array to Rows

Results:

num3nest2
13[11,12]
1313
16[14,15]
1616
19[17,18]
1919
Info

NOTE: Converting a column of arrays to rows unpacks the top level of the array only. You may have to apply this transformation multiple times.

Unnest array elements into columns

You can break out individual elements of an array into separate columns.

Info

NOTE: Each element that you want broken out into a column must be listed on a separate line in Path to elements.

 

Source:

arrayNested
["A",["B","C"],"D"]
["H",["I","J",["K","L"]]]
["E","F","G"]

The following transform retrieves the second and third elements of each array:

D trans
p03Value[2]
Typeref
p01NameColumn
p01ValuearrayNested
p02NamePaths to elements1
p02Value[1]
p03NamePaths to elements2
p04Valuetrue
p04NameInclude original column name
SearchTermUnnest Objects into columns

This one retrieves the first element of the array that is nested as the second element of the array:

D trans
p03Valuetrue
Typeref
p01NameColumn
p01ValuearrayNested
p02NamePaths to elements1
p02Value[1][0]
p03NameInclude original column name
SearchTermUnnest Objects into columns

The resulting data should look like the following:

arrayNestedarrayNested_1arrayNested_2
["A",["B","C"],"D"]["B","C"]B
["H",["I","J",["K","L"]]]["I","J",["K","L"]]I
["E","F","G"]F