Alteryx Designer Expert cheat sheet

Data Cleansing tool: replaces nulls, trims whitespace, modifies case, and removes unwanted characters; can also fix dates and numbers.

Select tool: reorders, renames, changes data types, and deselects fields; use 'Select All' then 'Deselect' to keep only needed fields.

Formula tool: creates new fields or updates existing ones using expressions; supports if/else, string functions, and math.

Multi-Row Formula: accesses previous or next rows (e.g., Row-1) to compute differences or running totals; requires a grouping field for partitioned calculations.

Data Blending: use Join or Union tools; Join combines based on common fields, Union appends rows from multiple sources.

Record ID tool: adds a sequential ID; useful for preserving order after sorting or for unique identifiers.

Sample tool: selects first N, last N, every N, or a random percentage of rows; useful for testing or reducing data volume.

Sort tool: orders records by one or more fields; can be used before Sample to get top/bottom records.

Unique tool: identifies and separates unique vs duplicate records based on selected fields; duplicates go to the 'D' output.

Text to Columns tool: splits a string field into multiple fields based on a delimiter, number of columns, or fixed width.

RegEx tool: parses or matches patterns; use 'Parse' to extract groups, 'Match' to filter, 'Replace' to substitute.

DateTime tool: converts between string and DateTime formats; use custom format strings like 'yyyy-MM-dd'.

Transpose tool: converts columns to rows (wide to long); select key fields and data fields to pivot.

Cross Tab tool: converts rows to columns (long to wide); specify grouping fields, header field, and value field.

Formula functions: IF/ELSEIF/ELSE/ENDIF, IIF(condition, true, false), and Switch for conditional logic.

String functions: Trim, Left, Right, Substring, Replace, Contains, StartsWith, EndsWith, and PadLeft.

Numeric functions: Round, Ceil, Floor, Abs, Mod, and Pow; use ToNumber to convert strings to numbers.

Multi-Field Formula: applies a formula to multiple fields at once; use [CurrentField] to reference the current field.

Join tool: combines two inputs based on common fields (Join by name or position); outputs Left, Right, and Join (inner).

Join types: inner (J), left (L+J), right (R+J), full outer (L+J+R); use Union to combine unmatched records.

Union tool: appends rows from multiple inputs; can match fields by name or position; 'Auto Configure' by Name for consistent schemas.

Find Replace tool: looks up values from a lookup table and appends or replaces fields; useful for mapping.

Append Fields tool: adds fields from a source to every row of a target, creating a Cartesian product.

Join tool 'Join by Position' is useful when field names differ but order is consistent; risky if order changes.

Multiple Join tools can be chained; ensure join keys are unique to avoid row multiplication.

Union tool can handle different schemas; use 'Output All Fields' to include all fields from all inputs.

Spatial tools: Create Points, Buffers, Distance, Trade Area, Spatial Match, and Spatial Info; require spatial objects (points, lines, polygons).

Spatial Match: finds spatial relationships (intersects, contains, within) between two spatial streams.

Trade Area tool: creates polygons around points based on drive-time or distance; requires an Alteryx spatial dataset.

Predictive tools: Linear Regression, Logistic Regression, Decision Tree, Forest Model; use Create Samples to split data.

Clustering tools: K-Centroids Cluster Analysis and Cluster Analysis; use to segment data.

Time Series tools: ARIMA, ETS, and TS Forecast; require a date field and a numeric target.

R and Python tools: integrate custom code; need to install packages and manage data types.

Spatial Object fields are stored as Blob; use Spatial Info to extract properties like area or centroid.

Standard Macro: a workflow saved as .yxmc; can have inputs and outputs; run as a tool in another workflow.

Batch Macro: runs a macro for each record in a control parameter; use to iterate over groups or files.

Iterative Macro: repeats until a condition is met; useful for loops; set max iterations.

Macro Input tool: defines input anchors; can be configured to accept tables or values.

Macro Output tool: defines output anchors; can output data or values.

Control Parameter tool: passes a single value or field to a macro; used in Batch and Iterative macros.

Macro can be saved to a repository and shared; use 'Manage Macros' to add to the tool palette.

Interface tools (e.g., Text Box, Drop Down) in macros or apps allow user input; use Action tools to update other tools.

Use 'Performance Profiling' in Workflow Configuration to identify slow tools; enable 'Run with Performance Profiling'.

Filter early: reduce data volume as soon as possible to improve performance.

Avoid unnecessary sorting; sort only when required.

Use 'Cache and Run Workflow' to save intermediate data and avoid re-running upstream tools.

Block Until Done tool: ensures a tool runs after all upstream data is processed; useful for file output ordering.

Debug with 'Message' tool and 'Test' tool to validate data and log messages.

Check 'Output' anchor of tools for errors; use 'View' > 'Output' to inspect data at each step.

Use 'Tool Container' to group and disable sections for testing; disable containers to skip parts of the workflow.

Limit use of 'Append Fields' with large datasets; can cause Cartesian products and memory issues.

Output Data tool: writes to various formats (csv, xlsx, yxdb, etc.); can output multiple files using 'Take File/Table Name From Field'.

Reporting tools: Table, Chart, Layout, Render; use to build custom reports; Render outputs to PDF, PNG, etc.

Table tool: configures data tables with grouping, sorting, and formatting; can include headers and footers.

Layout tool: arranges report elements (tables, charts, text) in a layout; can be vertical or horizontal.

Render tool: produces final output (PDF, PPTX, etc.) from layout; can also output to a printer.

Connectors: Input Data and Output Data tools support databases (e.g., SQL Server, Oracle), cloud (e.g., Snowflake, BigQuery), and files.

When writing to Excel, use 'Output Options' to specify sheet name, and 'Non-Data Fields' to skip headers.

Use 'File Format' options to set delimiters, encoding, and whether to include headers.

For dynamic output, use 'Take File/Table Name From Field' and specify a field containing the filename or table name.