Applying Formulas in Alteryx

Data Preparation in Alteryx

Deanna Sanchez

Alteryx ACE and Owner, Nova Geographica LLC

Various formulas are available

Organized by category and ready to use!

• Conditional
• DateTime
• Finance
• Operators
• String
• and much more

Formula expression builder

Uses the same formulas and syntax as the Filter tool custom filter.

• When syntax is correct, the expression will be highlighted in color.
• The Data Preview will show the result for the first record.

 

Data types are important

• String criteria require quotes
• Numeric criteria do not need quotes
• Ensure the formula uses fields with the correct data type

String formula examples

PadLeft adds leading zeroes to string fields.

• Syntax: PadLeft(String, len, char)
• Example: PadLeft([Product ID], 8, '0')

Business Case:

• Add leading zeroes to a Product SKU or ID.

String formula examples

The Replace formula replaces specific characters with string text.

• Syntax: Replace(String, Target, Replacement)
• Example: Replace([Business Name], 'Co.', 'Company')

Business Case:

• Replace abbreviations with full descriptions.

Flagging with the Contains formula

The Contains formula outputs a 0 or -1 and can be used to flag if a field contains criteria.

• Syntax: Contains(String, Target)
• Example: Contains([Stage], 'Final')

Business Case:

• Flag if records contain a specific text value.

Length-based vs. Position-based string formulas

• Length-based begins with a count of 1

  • Example Syntax: Left([Field], 3)

• Position-based begins with a count of 0

  • Example Syntax: GetWord([Field], 1)

Length-based formulas

• Length-based formulas begin with a count of 1.
• Left([Field], 3) results in "ALT".

 

Length-based formulas

• Length-based formulas begin with a count of 1.
• Left([Field], 3) results in "ALT".

 

Length-based formulas

• Length-based formulas begin with a count of 1.
• Left([Field], 3) results in "ALT".

 

Position based formulas

• Position-based formulas begin with a count of 0
• GetWord([Field], 1) results in "Preparation"

 

Position based formulas

• Position-based formulas begin with a count of 0.
• GetWord([Field], 1) results in "Preparation".

 

Position based formulas

• Position-based formulas begin with a count of 0.
• GetWord([Field], 1) results in "Preparation".

 

Position based formulas

• Position-based formulas begin with a count of 0.
• GetWord([Field], 1) results in "Preparation".

 

Numeric formula examples

Numeric formulas enable you to perform mathematical operations such as:

• Addition, division, Finance formulas, Math formulas, and more

 

Business Case:

• Calculate the percentage of quarterly sales out of total sales.

• Example: ([Quarterly Sales] / [Total Sales])*100

 

 

 

Fixed decimals

Fixed decimal data types have options to modify the Precision and Scale of the output.

• Default precision of 19 and scale of 6.

 

Fixed decimals

Precision is the length of characters for the entire output number.

¹ https://help.alteryx.com/current/en/designer/file-types-support/data-types.html#idm45521464713616

Fixed decimals

Precision is the length of characters for the entire output number.

• The character count includes:
  • All digits before the decimal point

¹ https://help.alteryx.com/current/en/designer/file-types-support/data-types.html#idm45521464713616

Fixed decimals

Precision is the length of characters for the entire output number.

• The character count includes:
  • All digits before the decimal point
  • All digits after the decimal point

¹ https://help.alteryx.com/current/en/designer/file-types-support/data-types.html#idm45521464713616

Fixed decimals

Precision is the length of characters for the entire output number.

• The character count includes:
  • All digits before the decimal point
  • All digits after the decimal point
  • The decimal point

¹ https://help.alteryx.com/current/en/designer/file-types-support/data-types.html#idm45521464713616

Fixed decimals

Precision is the length of characters for the entire output number.

• The character count includes:
  • All digits before the decimal point
  • All digits after the decimal point
  • The decimal point
  • A negative sign if required

¹ https://help.alteryx.com/current/en/designer/file-types-support/data-types.html#idm45521464713616

Fixed decimals

Precision is the length of characters for the entire output number.

• The character count includes:
  • All digits before the decimal point
  • All digits after the decimal point
  • The decimal point
  • A negative sign if required
  • Example: Precision of 10

Fixed decimals

Scale is the number of characters after the decimal point.

Fixed decimals

Scale is the number of characters after the decimal point.

• Example: Scale of 2

Updating fields and creating new ones

Updating existing fields

• Formulas can be applied to existing fields
• Data types cannot be modified

 

Creating new fields

• Use "Add Column" and create a new field
• Data types can be manually set

Stacking formulas in the Formula tool

• Click the plus sign to stack multiple formulas within one tool
• All new fields are available in subsequent formulas in the same Formula tool

Let's practice!

Data Preparation in Alteryx