Countif and Rank in designer

[tomekgagola]

Hi, I have been moving my work from excel to alteryx and now I have got a challenge I can not overcome. I want to replace count-if and rank from excel in alteryx. I used transpose with group by with nil luck.

Here is my data file:

System	Sub-System	Count	Rank
A	AA	3	1
A	BB	2	1
A	AA	3	2
B	BB	2	1
B	AA	3	3
B	CC	1	2

I have tried to get Count column that is Count-in on Sub-system and Rank column that shows the items rank when they appear in the the file. I need Rank to filter out further down the line.

[atcodedog05 · Accepted solution]

Hi @tomekgagola 

Here is how you can do it.

Workflow:

1. Using record id to map order.

2. Using summarize to get count.

3. Using join to map back the count.

4. Using multi-row formula tool to get rank.

5. Using sort tool to order back to the original order.

Hope this helps : )

[atcodedog05]

Hi @tomekgagola 

Rank is not matching. Can you please provide logic used for Rank.

[tomekgagola]

I see I have made an error with Rank. Thanks for the solution.

[atcodedog05]

Happy to help : ) @tomekgagola 

If my response helps please don't forget to mark it as solution.

Cheers and have a nice day!