Separate data based on each rank group and then apply filter logic

aparna0208

Hi,

I have a dataset that includes rank column where it is ranked based on advisor name. The input data is as follows

Name rand rank

Bob 0.9 1

Bob 0.2 1

Bob 0.4 1

Paul 0.5 2

Paul 0.6 2

Paul 0.4 2

Paul 0.1 2

I need to filter out data where rand is less than 0.3 for which I can simply use filter tool. The problem here is I need to first split data based on each rank and then apply that filter logic within each group. For example take all data from rank 1 and look for rand < 0.3 and output for that group. Next take all data from rank 2 and look for rand < 0.3 and output data for that group. So I want to split data based on each rank and then apply the filter. Is this something doable? Any help on this would be great. Thank you in advance!

Output

Accepted answers

Sebastiaandb

@aparna0208 Can you try to open the attached files? I changed the Alteryx version to 2020.2

Rankingmacro.yxmc

Ranking.yxmd

All comments

Sebastiaandb

@aparna0208

Here is my solution 🙂

In the macro you can enter the rankgroup you want to use and it will filter for <0.3.

Hope it helps!

Greetings,

Seb

Ranking.yxzp

aparna0208

@Sebastiaandb thanks for the inputs. I thought about macro but the problem with this is the whole thing needs to be automated and I'll schedule this workflow. It has to automatically group by rank and apply the filter within each group of data and then output it.

Sebastiaandb

@aparna0208 Is this what you want?

The batch macro makes it to process all groups individually and see it the rand's are <0.3. You can just attach the macro while uploading it to the gallery or refer to the macro by giving it a UNC path on a network drive that the server user has access to :-).

Hope it helps!

Greetings,

Seb

Ranking.yxzp

aparna0208

@Sebastiaandb I'm not able to download the worflow stating alteryx does not support using an earlier version of alteryx to open a workflow created with a newer version. Is there any other way that I could access this?

Sebastiaandb

@aparna0208 Can you try to open the attached files? I changed the Alteryx version to 2020.2

Rankingmacro.yxmc

Ranking.yxmd

aparna0208

@Sebastiaandb I opened both files but have issues with rank workflow where I'm not able to select a field for control parameter and it's erroring out because nothing is listed under that to pick a value. Not sure if I'm missing out on anything?

danilang

Hi @aparna0208

Can you provide a sample of your output? Do you need the output to be split into different streams within the workflow?

Dan

DawnDuong

hi @aparna0208

If I guess your required use case correctly, all you need is the ability to output different groups into different sheets(?) and limit such that values of rand <0.3 are filtered out.

In this case, you can simply filter all values that have rand <0.3 first and then use the Output tool to split the filtered data into separate sheets/files - refer to the help link here https://help.alteryx.com/20213/designer/output-data-tool

(Optional) Select Take File/Table Name From Field to write a separate file for each value in a selected field. Select the dropdown, and select an option:

Append Suffix to File/Table Name: Appends the selected field name to the end of the name of the selected table.
Prepend Prefix to File/Table Name: Prepends the selected field name to the beginning of the name of the selected table.
Change File/Table Name: Changes the file name to the selected field name.
Change Entire File Path: Changes the file name to the name of the selected field. The name must be a complete file path. This option can overwrite an existing file if a file exists in the full path directory.
1. Select Field Containing File Name or Part of File Name and select a field.
2. (Optional) Select Keep Field in Output

Dawn

aparna0208

@DawnDuong Thanks for your inputs. I'm not wanting to split the data into different tabs. I need to first filter values <0.3 rand within each group and then have all the values as one output

aparna0208

@danilang Not really split into different streams but all values as one output. Example as seen below

Input

Output

Sebastiaandb

@aparna0208 Awesome! No problem! Can you mark the post as the solution? If you need anything else, just let me know :-).

aparna0208

@Sebastiaandb I marked it as solution. I just have one quick question about macro. When I use the macro that you provided in my workflow, is there anything I need to change? You have used text input in that macro(snapshot as seen below) but in my workflow I'll connect to the output anchor of the multi formula tool which outputs similar data as the text input you have used. But I'm not quite sure if I can use the macro as is or do I need to change anything when I use it in my workflow?

Thank you!

Sebastiaandb

Hi @aparna0208 !

You can just use the macro after the multi formula tool :-).

The text input in the macro itself is just for test purposes.

As long as you have the rand and rank fields in the output anchor of your multi field tool, have that output anchor connected tot the question mark anchor and i anchor of the macro and have the settings (input3/control group by on rand and question on rank) right, you should be clear to go :-).

Greetings,

Seb

aparna0208

@Sebastiaandb Makes sense. Thank you so much for the clarification!

Wanted to check on another thing real quick. This macro is working as expected and I'm getting the desired output. I got a new requirement where rand value is not the same for all. Looks like there will be another file which will have a percent value for each person as seen below. I need to see what's the value for each person and convert it to decimal value and then use that as the rand value

For example

Name Percent New value

Bob 35 0.35

Will 45 0.45

Smith 65 0.65

As per above example, for bob the rand value will be 0.35(new value column) and all data where rand<0.35 for Bob. Next for Will need to output all data where rand <0.45 and so on. Can I update this macro to build this logic? Looking for some help. Thanks in advance

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703