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 r088
Excerpt

Evaluates a String input against the Array datatype. If the input matches, the function outputs an Array value. Input can be a literal, a column of values, or a function returning String values.

After you have converted your strings to arrays, if a sufficient percentage of input strings from a column are successfully converted to the other data type, the column may be retyped. 

Tip

Tip: If the column is not automatically retyped as a result of this function, you can manually set the type to Array in a subsequent recipe step.

D s lang vs sql

D s
snippetBasic

D lang syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value: parsearray(strInput) as: 'convertedArray'

parsearray(strInput)

Output: Returns the Array data type value for strInput String values.

D s
snippetSyntax

D lang syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextderive type:single value:parsearray(str_input)

parsearray(str_input)


ArgumentRequired?Data TypeDescription
str_inputYStringLiteral, name of a column, or a function returning String values to match

D s lang notes

str_input

Literal, column name, or function returning String values that are to be evaluated for conversion to Array values.

  • Missing values for this function in the source data result in null values in the output.
  • Multiple columns and wildcards are not supported.

D s
snippetusage


Required?Data TypeExample Value
YesString'[1,2,3]'


D s
snippetExamples

Example - parsing strings as an array

Source:

The following table represents raw imported CSV data:

setIditemsAitemsB
s01"1,2,3"4
s02"2,3,4"4
s03"3,4,5"4
s04"4,5,6"4
s05"5,6,7"4
s06"6,7,8"4

In the above table, you can see that the two items columns are interpreted differently. In the following steps, you can see how you can parse the data as array values before producing a new column intersecting the two arrays.

Transformation:

Create a new column to store the array version of itemsA:

D trans
RawWrangletrue
p03ValuearrA
Typestep
WrangleTextderive type: single value: itemsA as: 'arrA'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueitemsA
p03NameNew column name
SearchTermNew formula

Remove the quotes from the column:

D trans
RawWrangletrue
p03Value''
Typestep
WrangleTextreplacepatterns col: arrA with: '' on: `"` global: true
p01NameColumn
p01ValuearrA
p02NameFind
p02Value`"`
p03NameReplace with
p04Valuetrue
p04NameMatch all occurrences
SearchTermReplace text or pattern

Now create the array by merging the array text value with square brackets and then using the PARSEARRAY function to evaluate the merged value as an array:

D trans
RawWrangletrue
Typestep
WrangleTextset col: arrA value: parsearray(merge(['[',arrA,']']))
p01NameColumns
p01ValuearrA
p02NameFormula
p02Valueparsearray(merge(['[',arrA,']']))
SearchTermEdit column with formula

You can create the second array column using a similar construction in a new column:

D trans
RawWrangletrue
p03ValuearrB
Typestep
WrangleTextderive type: single value: parsearray(merge(['[',itemsB,']'])) as: 'arrB'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueparsearray(merge(['[',itemsB,']']))
p03NameNew column name
SearchTermNew formula

Since both columns have been parsed as array values, you can use the ARRAYINTERSECT function to find the common values:

D trans
RawWrangletrue
p03ValuearrIntersectAB
Typestep
WrangleTextderive type: single value: arrayintersect([arrA,arrB]) as: 'arrIntersectAB'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuearrayintersect([arrA,arrB])
p03NameNew column name
SearchTermNew formula

Results:

setIditemsAitemsBarrAarrBarrIntersectAB
s01"1,2,3"4[1,2,3][4][]
s02"2,3,4"4[2,3,4][4][4]
s03"3,4,5"4[3,4,5][4][4]
s04"4,5,6"4[4,5,6][4][4]
s05"5,6,7"4[5,6,7][4][]
s06"6,7,8"4[6,7,8][4][]

D s also
labeltype