Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

essemMLB
7 - Meteor

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
2019-03-2812345678901010101--**--02020202
2019-03-2819283746501010101--**--02020202
2019-03-2898765432101010101--**--05050505
2019-03-2756473829105050505

 

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'
2019-03-2833
2019-03-2710

 

Any help you can provide would be deeply appreciated!

4 REPLIES 4
florayaoyao
8 - Asteroid

 

 

@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

 

 

join table.JPG

 

 

nerces
8 - Asteroid

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.

 

StringDetectCountSum_77311.PNG

danilang
19 - Altair
19 - Altair

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
7 - Meteor

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

Labels