topic Re: Assigning values using IF loop when condition is met in Alteryx Server Discussions

Assigning values using IF loop when condition is met

ldave87 — Sun, 01 Mar 2020 18:16:20 GMT

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.

Re: Assigning values using IF loop when condition is met

DavidP — Sun, 01 Mar 2020 18:59:50 GMT

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

Re: Assigning values using IF loop when condition is met

ldave87 — Sun, 01 Mar 2020 19:24:57 GMT

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

Re: Assigning values using IF loop when condition is met

DavidP — Sun, 01 Mar 2020 21:20:53 GMT

Here you go

Re: Assigning values using IF loop when condition is met

ldave87 — Sun, 01 Mar 2020 21:52:51 GMT

Thanks David!!!