Alteryx Designer Advanced cheat sheet

Join vs Join Multiple: Join supports two inputs; Join Multiple accepts 2-256 inputs, requires at least one common field, and outputs a Cartesian product for unmatched key combinations unless filtered.

Union mode 'Auto by Name' matches fields by name only; 'Auto by Position' matches by field order; manual configuration lets each input be mapped individually and outputs only fields present in the configured set.

Find Replace has two modes: Append Field (adds matched value as a new column) and Replace (overwrites the original field text). Match options include Entire Field, Any Part of Field, and Start of Field.

Multi-Row Formula with Group By processes rows independently per group; the Row-1 reference resets to null at each group boundary, so group-aware running totals require the Group By setting.

Data Cleansing can remove null rows, remove leading/trailing whitespace, replace nulls with blanks, modify letter case, and remove punctuation; it does not deduplicate records (that is the Unique tool).

Unique tool outputs two streams: Unique (U) keeps the first occurrence of each key combination in input order, Duplicate (D) holds all subsequent matches. Field selection defines the key.

Fuzzy Match requires the Match function configured per field (e.g., Jaro, Levenshtein, Dice, Soundex); the Match Threshold (0-1) controls strictness, and a higher threshold reduces matches.

Formula and Multi-Field Formula treat nulls as null; ISNULL() and !ISNULL() test for null, while COALESCE or a Multi-Row Formula can substitute defaults across multiple fields.

RegEx tool modes: Match (returns parsed output only), Parse (tokenizes a field into numbered groups), Replace (rewrites the field), and Tokenize (splits text into one row per match).

In Alteryx RegEx, named groups use (?...) syntax; Parse mode exposes each capture group as a separately named output field, and unmatched groups return null.

Text to Columns splits on a delimiter or fixed width; the Split to Rows option creates one row per token, while Split to Columns creates new fields and requires a maximum number of columns.

Cross Tab aggregates the data field by the header field into columns; Summarize is often used upstream to pre-aggregate. The Group By field becomes row identity, and the Header field's distinct values become columns.

Transpose pivots wide fields into rows: Key Fields pass through, and Data Fields collapse into a Name/Value pair. Transpose followed by Cross Tab round-trips a pivot only when aggregation is unambiguous.

JSON Parse creates a schema from a JSON string field; the output JSON_Name/JSON_ValuePair fields feed into a Select or Cross Tab to flatten the structure.

XML Parse requires a field with a single root element; it outputs a flat Name/Value/Path hierarchy, and nested elements are addressed by their path, not by position alone.

The DateTime tool formats and parses dates using a specified Input/Output format; when the format string is wrong the result is null rather than an error, so validate with a null check downstream.

SpatialObj fields carry geometry; the Select tool or a Spatial Info tool reports the SpatialObj type (Point, Line, Polygon, MultiPolygon) and the number of parts.

Spatial Match modes include Intersects, Contains, Within, Touches, Crosses, and Overlaps; 'Contains' requires the target geometry to be entirely inside the source, while 'Intersects' includes any shared boundary.

Trade Area tool builds polygons from points using drive-time (requires a drivetime dataset), distance (radius), or grid methods; drive-time polygons are network-based and are not perfect circles.

Spatial Process tool can combine geometries (Combine), split multipart polygons (Decompose), or dissolve by a grouping field. Dissolve merges adjacent polygons that share the grouping key.

Find Nearest returns the nearest feature within a maximum distance and can limit results per target; it requires a spatial index or distance field and returns a distance field in the output.

Distance tool computes straight-line distance between two point spatial objects; for network or drive-time distances use the Trade Area or drivetime tools instead.

Spatial Info with 'Area' or 'Length' requires a projected coordinate system; geographic (latitude/longitude) data returns degrees, so reproject with the Project tool before measuring area or length.

The Project tool converts between coordinate systems using an EPSG code or a .prj file; spatial operations between layers should be performed in the same projection.

Linear Regression outputs a model object plus a report; the report lists coefficients, p-values, R-squared, and standard error. Predict with the Score tool using the model and new data.

Logistic Regression predicts a binary target and outputs probabilities; the cutoff for classification is set in the tool, and the report gives accuracy, precision, recall, and AUC.

Decision Tree outputs a tree model with a report showing node splits; the Score tool applies the model, and the Model Comparison tool evaluates multiple models on the same holdout.

Forest Model builds an ensemble of decision trees; it does not output a readable split report but provides variable importance, and typically outperforms a single tree on noisy data.

K-Centroids Cluster Analysis (K-means) requires a chosen k; the tool outputs cluster assignments and centroids, and the Cluster Diagnostics tool helps pick k via elbow or silhouette methods.

The Score tool requires a model object from a supported predictive tool; fields in the new data must match the model's predictors, and missing fields cause an error rather than being imputed.

The Oversample tool balances a skewed binary target by duplicating the minority class; it must be applied to training data only, not the holdout, to avoid leakage.

Correlation and Covariance tools produce a matrix of pairwise statistics; the Summarize tool's Correlation option computes correlation between two numeric fields but not a full matrix.

Standard Macro (batch): replaces a tool in a workflow, accepts an input and produces an output, and is saved as .yxmc. It runs on the full incoming dataset and is configured with a Macro Input/Output.

Batch Macro: iterates over incoming records, running the workflow once per group or row; the Control Parameter tool receives each iteration's value, and the Macro Output returns per-iteration results.

Iterative Macro: loops until a condition is met; the macro must contain a Macro Input/Output plus a Condition tool. The iteration count is set in the macro's Interface Designer properties.

Interface tools (Text Box, Drop Down, List Box, Check Box, Numeric Up/Down, Date, File Browse, Folder Browse) feed Action tools that update configuration of downstream tools.

Question constants (Q) feed Action tools; a Text Box question can update a Filter's comparison value, a Select tool's field list, or a Formula expression via the Action tool.

The Interface Designer (View > Interface Designer) sets the app's output mode (single file, multiple files, or a packaged workflow) and the chained app behavior.

The 'Update Value' Action tool writes a question's value into the selected tool's configuration; 'Update Field' changes field mapping, and 'Update Raw XML' writes arbitrary XML.

A chained app runs a second workflow after the first; the output of the first app must be a file that the second app consumes, and the chain is configured in the Interface Designer.

The Run Command tool executes external programs or scripts; it requires an input stream (or a Text Input), a command, and output configuration, and runs only when the workflow executes, not on save.

The Message tool writes to the workflow's message log and can halt the workflow on error; it is used for validation and debugging, and the message appears in the Results window.

The Test tool (Developer category) validates a condition and can throw an error or warning; it is often placed after joins to verify row counts.

The Cache and Run tool caches upstream data so downstream tools can be iterated without re-reading sources; clearing the cache forces a full rerun.

The Workflow Configuration (Runtime) tab sets the engine (original vs AMP) and the temporary directory; the AMP engine changes some tool behavior such as sort stability.

The Performance Profiler (View > Performance Profiler) reports per-tool execution time; it requires the workflow to run with profiling enabled and shows the slowest tools first.

The Find and Replace in the canvas (Ctrl+F) searches tool configurations; the Workflow Dependencies (Options > View Workflow Dependencies) lists all input/output paths and macros for packaging.

The Field Summary tool profiles each field's type, null count, and distinct values; it is faster than sampling with Browse and is used to validate data quality before transformation.

Input Data supports connections to files, databases (via ODBC/OLE DB), and Alteryx Gallery data; a connection can be saved as a 'connection' for reuse and is referenced by name.

Output Data options include overwrite, append, and 'Create New Sheet' for Excel; the 'Take File/Table Name from Field' option writes one output per distinct value of a field.

The 'Output Mode' for a multi-file output creates one file per group; the file name is built from the field value plus a prefix/suffix, and the path must exist or the tool errors.

For database outputs, the 'Pre-SQL' and 'Post-SQL' statements run before/after the write; 'Update; Insert if New' requires a primary key and is used for upserts.

The Select tool's 'Output Field Name' changes the output schema; renaming a field at the end of the workflow does not change the source, only the written output.

When writing to Excel, the field type must be compatible; a SpatialObj or Blob field cannot be written directly and must be converted (e.g., ST_AsText) first.

The 'Save to Gallery' output writes to Alteryx Gallery; the workflow must be published and the user must have write permission to the target studio.

A connection with a saved password stores credentials in the Alteryx settings; sharing the workflow does not share the saved password, so the recipient must re-authenticate.