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 r095

D toc

Excerpt

Extracting one or more values from within a column of values can turn data into meaningful and discrete information. This section describes how to extract column data, the methods for which may vary depending on the data type.

...

Extract vs. Split

Extract and split transformations do not do the same thing:

...

MethodDescription
By selectionSelect part of a value in the data grid to prompt a series of suggestions on what to do with the data. Typically, extract options are near the top of the suggestions when you select part of a value.
By column menuFrom the menu to the right of the column, select Extract and a sub-menu item to begin configuring a transformation. See Column Menus.
By Transformer toolbarAt the top of the data grid, click the Extract icon in the Transformer toolbar to begin configuring extract transformations. See Transformer Toolbar.
By Search panelIn the Search panel, enter extract to build a transformation from scratch. See Search Panel.

Extract text or patterns

...

The following functions can be used to extract values from a Datetime column, as long as the values are present in the formatted date:

Function NameDescription
DAY Function

D excerpt include
pageDAY Function
nopaneltrue

MONTH Function

D excerpt include
pageMONTH Function
nopaneltrue

YEAR Function

D excerpt include
pageYEAR Function
nopaneltrue

HOUR Function

D excerpt include
pageHOUR Function
nopaneltrue

MINUTE Function

D excerpt include
pageMINUTE Function
nopaneltrue

SECOND Function

D excerpt include
pageSECOND Function
nopaneltrue

You can also reformat the whole Datetime column using the DATEFORMAT function. The following reformats the column to show only the two-digit year:

...

Tip

Tip: If you set the number of patterns to extract to 2 for the address column, you might extract apartment or suite information.

Extract components of a URL

URL components

Using functions, you can extract specific elements of a valid URL. The following transformation pulls the domain values from the myURL column:

D trans
p03ValuemyDomain
Typestep
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueDOMAIN(myURL)
p03NameNew column name
SearchTermNew formula

In some cases, the function may not return values. For example, the SUBDOMAIN function returns empty values if there is no sub-domain part of the URL.

The following functions can be used to extract values from a set of URLs:

Function NameDescription
HOST Function

D excerpt include
pageHOST Function
nopaneltrue

DOMAIN Function

D excerpt include
pageDOMAIN Function
nopaneltrue

SUBDOMAIN Function

D excerpt include
pageSUBDOMAIN Function
nopaneltrue

SUFFIX Function

D excerpt include
pageSUFFIX Function
nopaneltrue

URLPARAMS Function

D excerpt include
pageURLPARAMS Function
nopaneltrue

Query parameters

You can extract query parameter values from an URL. The following example extracts the store_id value from the storeURL field value:

D trans
p03Valuestore_id
Typestep
p01NameColumn to extract from
p01ValuestoreURL
p02NameOption
p02ValueHTTP Query strings
p03NameFields to extract
SearchTermExtract patterns

Extract object values

If your data includes sets of arrays, you can extract array elements into columns for each key, with the values written to each key column.

...

D trans
Typestep
p01NameColumn
p01ValueEvents
SearchTermExpand arrays into rows

Extract

...

URL components

...

Values into a List

You can also extract sets of values into an array list of values. 

Tip

Tip: This transformation is useful for extracting types or patterns of information from a single column.

Extract matches into array

Using 

D s item
itempatterns
, you can extract specific elements of a valid URL. The following transformation pulls the domain values from the myURL columnthe values of the column to form a new column of arrays. The following example shows the usage of {any} pattern to extract the cell values and form a new array column.

Transformation:

 

D trans
DOMAIN(myURL)
p03ValuemyDomain`,`
Typestep
p01NameFormula typeColumn
p01ValueSingle row formulaproduct
p02NameFormulaPattern matching elements in the list
p02Value`{any}`
p06Value1
p03NameNew column nameDelimiter separating each element
SearchTermNew formula

In some cases, the function may not return values. For example, the SUBDOMAIN function returns empty values if there is no sub-domain part of the URL.

The following functions can be used to extract values from a set of URLs:

Query parameters

You can extract query parameter values from an URL. The following example extracts the store_id value from the storeURL field value:

D trans
p03Valuestore_id
Typestep
p01NameColumn to extract from
p01ValuestoreURL
p02NameOption
p02ValueHTTP Query strings
p03NameFields to extract
SearchTermExtract patterns

 

Extract matches into Array

Results:

BeforeAfter
socks, socks, socks["socks", "socks", "socks"]
pants, pants["pants", "pants"]

Extract hashtags

Include Page
EXAMPLE - Extract Values
EXAMPLE - Extract Values

D s also
inCQLtrue
label(label = "cleanse")