Preferences about designing Alteryx conditional logic: Formula Tool or series of Filters?

This is a more general Alteryx question about designing a workflow with conditional outcomes. Do Alteryx workflow designers prefer using a long If-Then-Else logic in a Formula Tool, or a series of Filter Tools as the condition and a Formula Tool after each for the result assignment?

Workflow

Tips and Tricks

Best Practices

Preparation

Accepted answers

Hi Philip,

It really depends on what you are trying to accomplish.

Scenario 1: One split, multiple criteria.

Preferred Solution: Use a filter tool with a custom filter where a formula can be applied (IF THEN ELSE) with your case statements.

Scenario 2: Multiple splits required with individual criteria requirements but can only meet 1 criteria per record.

Preferred Solution 2: Multiple tiered filters, one for each data set and apply the necessary transformation before unioning back together.

Scenario 3: Multiple splits required with individual criteria requirements but can meet more than 1 criteria per record.

Preferred Solution 3: Multiple side-by-side, one for each data set and apply the necessary transformation before unioning back together.

Scenario 4: Single substitution for case with a consistent data source and outcomes in a single column.

Preferred Solution 4: A formula tool with each of the cases named.

Scenario 5: Multiple substitutions in different columns with different data sources but named outcomes.

Preferrred Solution 5: Dynamic Replace tool with Boolean expressions. Good for complex replacement with a summary output of the performed substitutions, but can be tricky to set up.

Scenario 6: Multiple Columns with same formula applied.

Preferred Solution 6: Multi-Field Formula Tool with selected fields and single formula.

I think this covers most of the scenarios I use these for. Let me know if you need an example of any of them.

Regards,

Andrew

All comments

Hi Philip,

It really depends on what you are trying to accomplish.

Scenario 1: One split, multiple criteria.

Preferred Solution: Use a filter tool with a custom filter where a formula can be applied (IF THEN ELSE) with your case statements.

Scenario 2: Multiple splits required with individual criteria requirements but can only meet 1 criteria per record.

Preferred Solution 2: Multiple tiered filters, one for each data set and apply the necessary transformation before unioning back together.

Scenario 3: Multiple splits required with individual criteria requirements but can meet more than 1 criteria per record.

Preferred Solution 3: Multiple side-by-side, one for each data set and apply the necessary transformation before unioning back together.

Scenario 4: Single substitution for case with a consistent data source and outcomes in a single column.

Preferred Solution 4: A formula tool with each of the cases named.

Scenario 5: Multiple substitutions in different columns with different data sources but named outcomes.

Preferrred Solution 5: Dynamic Replace tool with Boolean expressions. Good for complex replacement with a summary output of the performed substitutions, but can be tricky to set up.

Scenario 6: Multiple Columns with same formula applied.

Preferred Solution 6: Multi-Field Formula Tool with selected fields and single formula.

I think this covers most of the scenarios I use these for. Let me know if you need an example of any of them.

Regards,

Andrew

Thanks, Andrew!

I find myself writing a lot of IF-THEN-ELSE data cleaning logic in Formulas (more of what I'm used to coming from a Python background), and was wondering how others handled similar cases. Do people keep it tucked inside a single Formula Tool, or do they make it really obvious visually with Filters etc. Your scenarios are a nice roadmap.

Example:

IF Contains(Lowercase( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2]), "oral")
OR (
	Contains(Lowercase( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2]), "swab")
	AND StartsWith( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2], "o")
)
THEN "Oral specimen"

ELSEIF Contains(Lowercase( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2]), "nasal")
OR (
	Contains(Lowercase( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2] ), "swab")
	AND StartsWith( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2], "n")
)
THEN "Nasal specimen"

ELSEIF Contains(Lowercase( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2]), "cloacal")
THEN "Cloacal specimen"

ELSEIF Contains(Lowercase( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2]), "fecal")
THEN "Fecal specimen"

ELSEIF Contains(Lowercase( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2]), "tracheal")
THEN "Tracheal specimen"

ELSEIF Contains(Lowercase( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2]), "virus")
THEN "Viral isolate specimen (specimen)"

ELSEIF Contains(Lowercase( [SPECIMEN_TYPE_LABEL spm/spm.4/cwe.2]), "lung")
THEN "Lung specimen"

ELSE ""
ENDIF

Quick Links

Tags

This months top contributors

AkimasaKajitani 389

Hollingsworth 335