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

[Philip]

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?

[andrewdatakim · Accepted solution]

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

[Philip]

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

[andrewdatakim]

I definitely understand. I go back and forth and the biggest factor for me depends on the easiest way to explain it to my coworkers in a way they can process it quickly. Sometimes it is quicker for them to process the workflow visually and others its about seeing the breakdown all together.