Alteryx Server

Find answers, ask questions, and share expertise about Alteryx Server.
Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music
SOLVED

Assigning values using IF loop when condition is met

Highlighted
6 - Meteoroid

I'm using web data where the data is spread across multiple rows.

 

What I'm trying to do is : assign 1 or 0 (or any values) whenever a condition is met. 

 

Example:

 

1.  001 John 1 

2.  001 unknown 1 

3.  001 unknown 1

 

1. 002 unknown 0

2. 002 unknown 0

3. 002 unknown 0

4. 002 unknown 0

 

These are two different subgroups. I want to SCAN across each different subgroups and code 1 across ALL the rows in subgroup one BECAUSE JOHN is part of it. 

 

I want to code 0 across ALL the rows in subgroup two, because JOHN is NOT there. In this case, I want the SAME value to be assigned to the entire subgroup if the condition is met.  

 

The challenge is the subgroups don’t have the same number of rows. Some Subgroups are = 3, some are 60 or 100. Hence I want to use a loop statement. 

Highlighted
16 - Nebula

Let’s assume field1 is the subgroup code and field2 is the field containing John or unknown.

 

use Summarize tool: group by field1, concatenate field2.

 

Now, you can use a formula tool to create a new field [code] with formula:

 

if contains([concat_field2],’John’) then 1 else 0 endif.

 

now do text to rows on [concat_field2]

 

I can mock this up for you later if you’re having troubles 

Highlighted
6 - Meteoroid

Thank you so much David!!! A mock up would be much appreciated 

Highlighted
16 - Nebula

Here you go

 

DavidP_0-1583097645114.png

 

Highlighted
6 - Meteoroid

Thanks David!!!