Repeat part of the workflow for first/second best value

[matheusld2]

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

[MarqueeCrew]

Yes.  

inside of the CReW macros there is a dynamic formula tool. Using that formula tool you can construct a formula that searches across the data without transposing it.  

it sounds like a fun use case to solve for.   In gallery I have a package, hello world.  Inside of that silliness I do something similar.  

cheers,

 mark

[bkurt]

Hi Matheus,

it seems to me that you could add every row to a string and take the left 2 and the 2 after that values.

You can do it with the following formula.

Best value: Left([A}+[B]+[C]+[D], 2)

Second best: Right(Left([A}+[B]+[C]+[D], 4),2)

Kind regards,

Bora

[matheusld2]

Hello!

Thanks for your answer. I tested here and apparently it would only if the values on the 5 columns were the same length, which is not the case. Any ideas os how to proceed? Thanks. 

[bkurt]

Hi matheus,

honestly I'd have to get a better understanding of the data you are working with. Probably, you could work out something with REGEX.

For now, I think you could try to use the following formulas for best and second best.

 Best solution

IF !ISEMPTY([A]) THEN [A] ELSEIF !ISEMPTY([B]) THEN [B] ELSEIF !ISEMPTY([C]) THEN [C] ELSEIF !ISEMPTY([D]) THEN [D] ELSEIF !ISEMPTY([E]) THEN [E] ELSE "Error" ENDIF

Second best

IF [Best solution]=[A] THEN IF !ISEMPTY([B]) THEN [B] ELSEIF !ISEMPTY([C]) THEN [C] ELSEIF !IsEmpty([D]) THEN [D] ELSEIF !IsEmpty([E]) THEN [E] ELSE IF [Best solution]=[B] THEN IF !ISEMPTY[C] THEN [C] ELSEIF !IsEmpty([D]) THEN [D] ELSEIF !IsEmpty([E]) THEN [E] ELSE IF [Best solution]=[C] THEN [D] ELSEIF !IsEmpty([E]) THEN [E] ELSE IF [Best solution]=[D] THEN IF !ISEMPTY([E]) THEN [E] ELSE "No second best" ENDIF

You should try to tweek this code a bit until it works. For now I can't do more for you without the data.

Good luck!

[danilang · Accepted solution]

hi @matheusld2 

You can also concatenate the fields with a delimiter and then use regex to pull out the Best and Second result

Dan