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!

[aparna0208]

It worked. Thank you so much for all your inputs and help on this:) @Sebastiaandb 

[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