Hello!
I hope you're doing well.
I have the following situation: I have 5 different columns with different types of data and I need to find the first and second-best values. The rule is: data in column A>B>C>D>E (image below).
Right now I'm assigning those columns a numeric value (A =10, B=9, etc), then doing a transpose and getting the max value of that number I assigned. The problem with this approach is the size of the table (millions of records, and 70+ columns) that multiple when the transpose happens and slows the process considerably.
I also tried the following: when column A is populated, that's the best value and I separate those rows; then I filter when A is null, and will only check if there's a value in B for those rows, and if there is, that's the best value; then, when I'm done with all of the 5 steps, I stack those separate pieces in a single table. The problem with this is that I don't know how to repeat logic to get the second best.
Any ideas on what's the best way to achieve that result?
Thank you
