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

...

You can use the following command to write a TOO LONG message when the length of the first_name field exceeds 32 characters:

d-

...

showNotetrue

...

trans
Typestep
p01NameColumns
p01ValueString_test
p02NameFormula
p02ValueIF(LEN(first_name) > 32

...

, 'TOO LONG'

...

,String_test)
SearchTermEdit column with formula

Truncate Strings

The above test allows you to evaluate individual strings that are too long to see if they are errors or can somehow be shortened. For a large dataset in which you cannot easily solve these problems, you can simply choose to cut off the length of a string at 32 characters:

d-

...

trans
Typestep
p01NameColumns
p01Value*
p02NameFormula
p02ValueLEFT($col,32)
SearchTermEdit column with formula

In the above, you can use a wildcard to match all columns in the dataset. The replacement value is defined to be the first 32 characters of the source column ($col). By definition of the LEFT function, columns that are shorter than 32 characters in length are untouched.

...

  1. Select the first five digits of one of the nine-digit zip codes.
  2. In the suggestion cards, select the Extract card.
  3. Select the following variation:

    d-

    codeextract col: zipcode on:

    trans
    p03Value`{zip}`

    after:

    Typestep
    p01NameColumn to extract from
    p01Valuezipcode
    p02NameOption
    p02ValueCustom text or pattern
    p03NameText to extract
    p04Value`{start}`
    p04NameStart extracting after
    SearchTermExtract text or pattern

  4. Click Add.

The above solution references two

D s item
itempatterns
rtrue
 to identify elements of the cell value. For more information, see Text Matching.

...

Use the following transform to reduce a string to the rightmost 6 characters in any value:

d-

...

trans
Typestep
p01NameColumns
p01ValueprodID
p02NameFormula
p02ValueRIGHT(prodID, 6)
SearchTermEdit column with formula

Substring Values

The SUBSTRING function enables you to designate a specific subset of the string's characters to use. You specify the index of the first character in the values and the number of subsequent characters to include. For example, when applied to the value United States of America in the countries column, the following transform sets the new value to be States.

d-

...

trans
Typestep
p01NameColumns
p01Valuecountries
p02NameFormula
p02ValueSUBSTRING(countries, 7, 6)
SearchTermEdit column with formula

Note that the index value begins at zero; to extract from the beginning of the value, replace 7 above with 0.

...