community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

count(ID) over (partition by ID ) number_of_occur in Alteryx

Highlighted
Atom

I have a data that looks like this :  i want to create a field called Number_of_occur in alteryx 

IDname
1desmond
1desmond
2john
3mary
3mary
3mary
4jake
4jake

so that the output will look like this.

 

IDnamenumber_of_occur
1desmond2
1desmond2
2john1
3mary3
3mary3
3mary3
4jake2
4jake2

this is how you will do it in sql      :count(ID) over (partition by ID ) number_of_occur

 

Can somone help me. Thank you

Moderator
Moderator

Hello @dappiah 

You are able to accomplish this with the Summarize tool, and a join tool in Alteryx.

With the Summarize tool, you will want to set up the following:

Group By ID

Group By Name

Count ID

Join this back into your source data and remove the duplicate fields.

 

Ive attached a sample workflow to show you how I did this.


Thanks!

Trevor

Community Moderator
Labels