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 next

...

  • The following transforms might make original row information invalid or otherwise unavailable. In these cases, the reference returns null values:
    • pivot
    • flatten
    • join
    • lookup
    • union
    • unnest
    • unpivot
  • When working with datasets sourced from Parquet files, the $sourcerownumber reference is not supported.



Example:

The following example generates a new column containing the source row number for each row in the dataset, if available: 

d-

...

trans
RawWrangletrue
p03Value'src_rownumber'
Typestep
WrangleTextderive type: single value: $sourcerownumber as: 'src_rownumber'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value$sourcerownumber
p03NameNew column name
SearchTermNew formula

If you have already used the $filepath reference, as in the previous example, you can combine these two columns to create a unique key to the source of each row:

d-

...

trans
RawWrangletrue
p03Value'src_key'
Typestep
WrangleTextderive type: single value: MERGE([src_filename,src_rownumber],'-') as: 'src_key'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueMERGE([src_filename,src_rownumber],'-')
p03NameNew column name
SearchTermNew formula

$col

The $col variable is a reference to the column that is currently being evaluated. This variable references the state of the current dataset, instead of the original source.

...

In the following example, all columns in the dataset that are of String type are converted to uppercase:

d-

...

trans
RawWrangletrue
Typestep
WrangleTextset col: * value: IF(ISMISMATCHED($col, ['String']), $col, UPPER($col))
p01NameColumns
p01ValueAll
p02NameFormula
p02ValueIF(ISMISMATCHED($col, ['String']), $col, UPPER($col))
SearchTermEdit column with formula

In the above, the wildcard applies the edit to each column. Each column is tested to see if it is mismatched with the String data type. If mismatched, the value in the column ($col) is written. Otherwise, the value in the column is converted to uppercase (UPPER($col)).

...