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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

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

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

Highlighted
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

Labels