Assign rank based on value

[golfromeo]

Hello all,

I'm trying to achieve something as illustrated in the attachment, which is to assign a rank based on a hypothetical value to be looked up in another table.

I found a past discussion that almost does what I need it to do [link], but not for the format in which I have my data and I haven't been able to figure out a solution.

I would appreciate any help, thank you!

[mceleavey]

Hi @golfromeo ,

those number are not related. Can you explain why the combination of the two inputs would give the output required?

Why is Segment 2 Ranked 5? Why is Segment 1 Ranked 2 etc.

We will need to know the logic. Is it the hypothetical sales determine the rank by looking up the closest value in the corresponding segment in Input 1?

Thanks,

M.

[Luke_C · Accepted solution]

Hi @golfromeo 

I prepared the attached workflow, from what I can gather we're looking for the record that is closest to the hypothetical sales without going over.

1. I join the two data sets based on segement
2. Then I filter out records where sales exceed the hypothetical sales
3. Then I sort by segement/sales
4. Then sample the first record for each sale (the highest sales without going over)

[golfromeo]

Thanks for your reply and sorry if I didn't explain it clearly enough at first. The idea is to know what rank would this company have if they achieved sales of a certain amount. For example, in Segment 1, if they achieved sales of 8,500, they would be positioned 2nd replacing Carrier B. In Segment 2, by achieving 7,500, they would be 5th replacing Carrier E. Thanks again.

[golfromeo]

This works, thanks for your help!