find top 2 pay rate within same department

[thua]

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.

[alexnajm · Accepted solution]

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.

[alexnajm · Accepted solution]

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

[alexnajm · Accepted solution]

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.

[alexnajm · Accepted solution]

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?

[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!