find top 2 pay rate within same department

Hi guy,

Please help with: What tools and solution for finding list of top 2 highest pay within same department in output.

Information about data: a column name "Pay rate" with various pay rate together with a column name "Department" which include 6 different departments. The data has 100 rows.

image.png

Help

Accepted answers

alexnajm

Convert pay rate to a numeric data type (best with a formula tool) —> Sort data by Pay Rate descending —> Sample 1st N records with n=2 and grouping on department

alexnajm

Did you select Department in your grouping in the Sample tool? It looks like you are right there

alexnajm

If you also need to break it down by top 2 per department per score, you can also select score as another grouping column in the sample tool

alexnajm

Happy to help! If you could then mark one of my responses as a solution, that would be great. Thanks in advance!

All comments

alexnajm

Convert pay rate to a numeric data type (best with a formula tool) —> Sort data by Pay Rate descending —> Sample 1st N records with n=2 and grouping on department

thua

Can you explain in more details?

So I need to find a list including top 2 employee within each department (there are 6 departments) in the 100 records, which result supposed to be 12 rows for 2 employees x 6 department in totals. But I don't know how to sort 2 top emp + same department in a column with multiple department.

I break it down like the image (have 2 departments now as 2 sets of tools, but then it means I have to do it 4 more sets which is too much, is there another way to do that?) Also I will need to combine the 6 sets at the end for the output list.

image.png

alexnajm

Did you select Department in your grouping in the Sample tool? It looks like you are right there

alexnajm

If you also need to break it down by top 2 per department per score, you can also select score as another grouping column in the sample tool

thua

Ohhhhhhhhhhhhh I got this

Thank you very muchhhhhh. I got to understand more about Sample tools thank to youuu. Thank you very much.

image.png

alexnajm

Happy to help! If you could then mark one of my responses as a solution, that would be great. Thanks in advance!

thua

Sure, can you please help me with one more, I need to convert the "Date of Termination" to "Date" field. But when I do it, it's not working, appear as null and error in the images. Am I missing something there?

image.png

alexnajm

It’s not in the YYYY-MM-DD format so a select tool won’t work. Use a DateTime tool and convert it that way! You can remove the original string field afterwards with a select if needed

thua

I picked DateTime tool but it's not working too. But I think It was something wrong in the assignment format. But thank you very much for helping me!! I appreciate it!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703