Detecting a value in a string, then counting it as 1 in a new column

[essemMLB]

Alteryx community,

I have a record set where Column A contains a date in YYYY-MM-DD format, Column B contains a unique identifier, and Column C contains a string with several eight-digit values separated by an unusual delimiter. What I'd like to do is create a new column (actually, I want to create several but for the purposes of this thread I'll assume whatever process is executed for one new column can be executed for the rest) that returns a value of "1" in the cell if Column C contained a certain value. 

(Note: I'd like to avoid using Text-to-Columns because Column C could include dozens of values, so it'd get pretty unwieldy). 

An example:

Date	Unique ID	Value
2019-03-28	123456789	01010101--**--02020202
2019-03-28	192837465	01010101--**--02020202
2019-03-28	987654321	01010101--**--05050505
2019-03-27	564738291	05050505

In the table above, there are four unique IDs across two days. The three unique IDs present on 2019-03-28 have the '01010101' value, while the single unique ID present on 2019-03-27 does not. My hope is to use the SUMMARIZE tool to group by date, then count the number of unique IDs, and count the total number of rows in which the '01010101' value was present. The end result should transform to this:

Date	Unique IDs	Count of '01010101'
2019-03-28	3	3
2019-03-27	1	0

Any help you can provide would be deeply appreciated!

[florayaoyao]

@essemMLB    Hi,  I will recommend  to create a  new field that flags if the records contains "01010101".  Then Group by into two sets ( one just count the distinct number of ID, and another one only count the numbers of records where the flag shows "Y" - use a "filter"  before grouping) ..  

Then, join the two groups by date.  For the items that with no "01010101" contained, it might not show any result, so after the joining, you will also need to "Union" the data from both 'Left" and "Joined" results.     

In the end, I applied the data cleaning  (which replaces the null value by 0).    Please see the sample workflow I attached here. Hope this is helpful!

Thanks

Flora

[nerces · Accepted solution]

Here is my solution. You can add more columns by just duplicating the if statement and replacing it with the column name you want to add.

[danilang]

Hi there @essemMLB 

In your example do you care about the other unique ids, 02xxx and 05xxx or are you only ever interested in 01xxx?

Dan

[essemMLB]

Thanks everyone! This worked fine enough, and I was able to build the rest of the pipeline afterwards!