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!

[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

[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

[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 · Accepted solution]

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

[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

[Sebastiaandb]

@aparna0208 You have to connect the input file also to the question mark input of the macro :-). 

[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

1. (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