Getting a distinct count based on distinct criteria if a condition is met

[richleeb2]

Hi all,

I'm wondering if there is a way to get a total count for the following.  For anything that is successful under "Status" for a unique ID, I want to get a count of the number of distinct forms (with some of them being separated by delimiter comma)

123A has successful in both rows, so it counts 2 distinct forms (form1, form2)

345F has successful in only one row, so it counts 2 (form1, form2 )

678S is 1

897R is 0 since both are unsuccessful

344P is 0

234S is 3 since both rows are successful

So the total is 8.  Any idea how to do this in alteryx?

[Luke_C]

Hi @richleeb2 

Here's my pass at this. First I filtered to successful records, then split the forms to individual rows rather than being concatenated (i.e. 'form1, form 2' would become two rows), then I removed duplicates and summarized the data to get the count of 8.

[richleeb2]

Thanks, this got to the right number!  But I would like to append the individual count to the original table by ID (alhough the IDs are duplicated for some).  Or just the one total on the first line of the table.  Right now if I try to append fields, it attaches the total to every line.

[Luke_C]

Hi @richleeb2 

How's this?

[richleeb2]

Thanks, but the output is double counting some of the forms.  For example, 123A is showing 2 for both rows instead of just one row, so when you add up the total, it will be more than expected.  Basically the count should only append to the first unique locator.

[Luke_C]

Hi @richleeb2, sorry misinterpreted your note "although the IDs are duplicated for some" as it being expected behavior.

Try this, I removed dupes, joined, and then unioned the dupes back. This should be enough for you to either get the desired result or modify a bit to get what you need. 

[richleeb2]

This worked, thank you!!

[richleeb2]

@Luke_C 

Hi Luke, I was wondering if there was an alternative way of presenting the data.  I added a Text to Columns tool to the end because I want a count of each distinct form by ID (but no duplicates).  So for example:

123A, successful, form1 = Count is 1

123A , successful, form2 = Count is 1

123A, successful, form1 = Count is 0 because you already counted form1 above

Any help would be appreciated!

[Luke_C]

Hi @richleeb2 

Hope you had a good weekend. For your question, this is already sort of being done in the initial part of the workflow. I think by tweaking this area or the summarize tool you could get what you want. The first text to columns tool splits out the rows that have multiple forms listed in one record. Then the remove duplicates tool removes duplicates (i.e. 123A form 1) prior to counting by ID. If you update the summarize tool to group by ID and Form, then union the 'D' anchor of the unique tool, you should be able to get something like what you describe above. 

[richleeb2]

Thanks Luke, but I don't want to delete any rows because my actual data has other columns with data.  But I'll play around with it some more.