Forum Discussion
Formula challenge: re-shape and cleanup
Firstly, my congratulations on a very efficient and compact solution.
On the MAKEARRAY discussion, I do not think the function is itself a major problem, provided each calculation is a scalar evaluation. Where I think I hit problems is where the each calculation is actually a full array calc but uses INDEX to restrict the return to a single value. Once each value comes at the price of a full array calc, the time builds as O(n²).
My solution was both more complicated and more expensive than yours (x3) but it did allow me to explore some techniques that I thought might be usable for other problems.
= LET(
Fnλ, HSTACK(FirstWordλ,FinalWordλ,Identityλ,Identityλ),
baseTbl, WRAPROWS(TOCOL(alumni,1,1),3),
extendedTbl, CHOOSECOLS(baseTbl,{1,1,2,3}),
u, SEQUENCE(ROWS(extendedTbl),,1,0),
Fnsλ, CHOOSEROWS(Fnλ,u),
processedTbl, MAP(Fnsλ,extendedTbl,Applyλ),
NoBellλ(processedTbl)
)The key part of my experiment was to build an array of Lambda functions of the same dimension as the table to which it was to be applied. Then the functions could be applied one by one using MAP. My take is that the approach involves some pretty heavy computational steps but it remains O(n), so should not lock up as the problem size increases.
Thanks for sharing, Peter. I like your approach. It's very clean and easy to follow. MAP has tremendous potential when used with multiple arrays. Later when I'm at my other computer I may extended the rows down to 1 million and have at it with the timer. CHOOSECOLS/CHOOSEROWS/TOCOL/HSTACK appear to be very efficient calculation-wise in handling large arrays.
When I designed this exercise, I was curious how others would go about removing the 'bellman' records. From the other discussion, I recalled SergeiBaklan mention your use of FILTER possibly slowing down your calculation so I went in determined not to use FILTER. Ultimately, I used it thinking it would run smoothly if stored inside the NOBELL LAMBDA and called from my main formula. FILTER seems to be best approach to exclude records at the moment.
- Speaking of how people would do the removal of 'bellman' records I noticed in a couple of solution I thought it could be more efficient if that step was moved up earlier. If I read the code right, it looked like it could be done before the splitting of the first and last name in some cases which means that name splitting step would act on a slightly smaller set.
I've put thought into removing the 'bellman' records as early as possible. The way I designed this problem (Placing blank rows intermittently) it makes it very difficult to pull those records until before using TOCOL and WRAPROWS. I've considered using MAP to pull the records but the blank rows are an issue (and using MAP to cleanup before TOCOL/WRAPROWS is probably not very efficient). I had also considered BYROW to identify the rows to be used in the array but again, removing the blank rows first would have to be done. FILTER appears to be the best option at the moment. Maybe the calculations are a bit quicker if the 'bellman' records are pulled earlier. It would certainly have to be done if the record set was much larger and the 'bellman' records had to be removed to allow HSTACK to run.
My first draft of this exercise actually included arbitrary header information every thousand rows or so (The sheet would've appeared to have been pasted from a poorly designed Word document). I like creating a puzzle and working towards a solution when it seems impossible. I might revisit that first draft.
- If you wish to add to the pain, delete Ron Palmer's email address (row 2) and see what happens
It has been an interesting exercise; we have learnt something about the limits of HSTACK, but it has also pushed me into trying strategies that were previously only possibilities. I had to modify your named Lambda to apply it at the start of my formula without invalidating the existing version
NoBellλ = LAMBDA(arr, col, FILTER(arr, CHOOSECOLS(arr, col) <> "Bellman"));Overall, I was pleasantly surprised at how easy it was to return to an existing formula and modify it using modern methods. A traditional formula nested 7-deep would not be fun though, to be fair, there would probably have been a whole raft of helper columns to ease the pain.
For me, the function that I am most likely to reuse is
Applyλ = LAMBDA(λ, x, λ(x));Lambdas don't have to be long to be useful!
SergeiBaklan might know better, but I do not think FILTER is especially slow, given that it evaluates the entire criterion for each row. The conditional aggregation functions are faster because they don't evaluate secondary conditions once the first condition fails. If you are only trying to extract one or two matches XLOOKUP would be a preferred option because of its bisection search which requires order O(ln(n)) steps.
