Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multiple criteria formula help

richleeb2
8 - Asteroid

Hi,

 

I'm trying to use the formula tool to add another column with output dependent on certain rules below and concatenate the result in the output column shown

 

Rule 1: None of 4 columns can be blank

Rule 2: None of 4 columns can have the word "grape"

Rule 3: Col 2 must be a whole number

 

Col 1Col 2Col 3Col 4Output
pear4 bananaCol 3 cannot be blank
apple3.567applegrapeCol 2 must be whole number, Col 4 cannot be grape
strawberrykiwigrape Col 2 must be whole number, Col 3 cannot be grape, Col 4 cannot be blank
7 REPLIES 7
Luke_C
17 - Castor
17 - Castor

Hi @richleeb2 

 

Here's one way:

 

  1. Transpose the data
  2. IF statement for each piece of logic
  3. Concatenate results and join back

 

Luke_C_0-1660933299232.png

Luke_C_1-1660933343530.png

 

 

 

richleeb2
8 - Asteroid

Thank you!  How do I add a space after each comma?

phottovy
13 - Pulsar
13 - Pulsar

I had a very similar approach to @Luke_C . I always recommend pivoting your data instead of writing formulas across multiple columns. 

 

phottovy_0-1660934009682.png

 

Luke_C
17 - Castor
17 - Castor

@richleeb2 

 

Add the space in the separator in the summarize tool:

Luke_C_0-1660934083169.png

 

 

richleeb2
8 - Asteroid

Thank you Luke, this worked perfect but I want to add one more criteria to Col 2.  In addition to it must be a whole number, it cannot be blank.  How do I update the formula?

 

IF IsEmpty([Value])
Then [Name] + ' cannot be blank'
Elseif Contains([Value],'N/A')
Then [Name] + ' cannot be N/A'
Elseif !IsInteger([Value]) and [Name] = 'Rehosting Question 2'
Then [Name] + ' must be a whole number'
else null()
endif

Luke_C
17 - Castor
17 - Castor

Hi @richleeb2 

 

If column two is left blank it should already be handled by the logic because it is checking for all fields being blank. Please provide more information if that's not working correctly with your data. Otherwise be sure to accept the solution.

richleeb2
8 - Asteroid

Sorry I meant 0 but I figured it out!

Labels
Top Solution Authors