Alteryx designer Discussions

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

Distinct Count

Highlighted
8 - Asteroid

I need to create a new field 'Account Count', in order to count how many accounts I have. I have been attempting to use the field Account # to do a distinct count using the summarize tool. The problem is that many accounts have more than one date, business unit, and other fields that vary and are unique, all under the same account number.

 

For example:

Record 1 =  Account: 1, Subscribe Date: 2018, Business Unit: A

Record 2 =  Account: 1, Subscribe Date: 2018, Business Unit: B

Record 3 =  Account: 1, Subscribe Date: 2019, Business Unit: A

 

In a situation like this, I am getting Distinct Count = 3, for that account number.

 

How can I get the distinct count of account #'s while ignoring all other fields other than the Account number??? I will need to add the "account count" field back into the original dataset in order to filter by business unit and such and get those counts.

Highlighted
Alteryx Partner

hi @MPCNA,

 

this solution work for me:

 

1 select tool (i'm a lazy person so tdidn't add a tostring into the formula tool to transform all the data type in string)

 

1.PNG

 

2) formula tool to combine the 3 fields:

 

into a new field 

[account]+'-'+[subscribe date]+'-'+[B.U]

 

3) just set the summarize tool as you can see into the img

 

solution.PNG

 

 

8 - Asteroid

It looks to me that it is still counting Account 1, 3 times. For account1 in your example, I need it to only bring back 1 as the count.

Highlighted
Alteryx Partner

just share an example of your data. D:

because i don't understand your set of data.

 

if you have something like that:

 

1-2018-A
1-2018-B
1-2019-A
2-2018-a
2-2018-a

 

just split to column (and get the first coulmn) and after group by the new column with a summarize tool. 

Labels