Select Highest volume clients based on certain criteria

[collinjd]

Hi There,

I have a file of 5 million records in a csv file which I have created a table based on client ID and volume of transactions. What I now want to do is select the top 5 or 10 clients (based on client ID) with highest volume and returning all fields in the input file. 

ClientID   Volume

AB1              1,500,00

AB2               500,000

AB3               200,000

AB4               100,000

AB5                 50,000

What is the best way to do this, also 1 particular client has 1.5 million transactions which is more than 1 million rows in excel (I need the output in excel) and would need that split into 2 tabs.

Any suggestions on how to do this?

Thanks Jodi 

[jdunkerley79 · Accepted solution]

I suggest you use a sort tool to order the table by Volume descending.

You can then use a Select tool to keep the first 5 or 10 records as you need.

After this, you can join back to the records CSV file to keep just those entries.

I would then use a multirow tool to produce a row id. Then using a formula this could be converted into a sheet name so only x transactions are kept on each sheet.

Finally in the output tool you can select to use a field to determine which sheet it is written too.

Hopefully enough to get you started but happy to put a sample together if you want

[collinjd]

Great thanks, I will try this and come back to you if needed. Thanks for your help!

[collinjd]

Wow thank you this worked!