This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
As usual, I'm almost certain there will be quicker ways to do this but I got the exact results so I'm pleased none the less. I'm sure @mceleavey will have a smarter way of doing it with a tool I'm yet to understand given I'm not remotely technical.
I first loaded the first input into the arrange tool, which is perfect for arranging multiple columns of the same data into a single column without having to pivot the data using transpose, then grouping into like for like records and pivoting back using the Cross-tab tool. This is fine on smal data sets but when you're looking at larger data sets then that comes with an overhead.
After that, I unioned the annual data sets and joined to the first on Pick number. The records from the annual sets that did not match are the ones to which we assign the value 2. After that, it was a simple matter of grouping and summing the data accordingly, creating the calculation, sorting by year ascending and the calculated field descending then using a sample tool to take the first three records grouped by year:
I was going to build a macro to fix the structure on the Draft Pick Value, but it wasn't worth the time. Brute force today! Edit: Adding a second image to show my revised solution. Totally forgot the re-arrange tool existed until I was walking my dog last night!