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
Solved! Go to Solution.
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
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
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.
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!
hi @matheusld2
You can also concatenate the fields with a delimiter and then use regex to pull out the Best and Second result
Dan