Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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


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:


DateUnique IDValue


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:


DateUnique IDsCount of '01010101'


Any help you can provide would be deeply appreciated!




@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!






join table.JPG




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.




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?




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