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.
Solved! Go to Solution.
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
Thank you so much David!!! A mock up would be much appreciated
Thanks David!!!