Forum Discussion
Array of arrays using Lambda helper functions
PeterBartholomew1Just to get rid of that pesky blank row:
=REDUCE( "", ListCountry#, LAMBDA(acc,val, IF( TYPE(acc)<>64,TRANSPOSE(FILTER(StartList[ATHLETE],StartList[COUNTRY]=val)), VSTACKλ(,acc,TRANSPOSE(FILTER(StartList[ATHLETE],StartList[COUNTRY]=val))) ) ) )I think since MAP will iterate over an array of any shape, the results have to fit back into the same shape; I'd be interested to know if this is a mistaken assumption on my part.
I am not sure that represents a direction of travel that I will be following! After all, I passed on Charles's CORKSCREW function and requested the more limited ACCUMULATE function. Nevertheless, it opens my eyes to the possibilities. It strikes me as pushing back the boundaries of the Excel formula as a program.
1. Data input is provided by full access to the workbook names
2. Multiple calculations are taken forward simultaneously
3. This is probably out of scope for SCAN so REDUCE and MAKEARRAY have been used instead (does this imply that the first steps are repeated for every subsequent time period?)
4. Results are output to a contiguous region, multiple arrays being represented within one block.
Have I got this right, or are there things I have misunderstood even at this stage?
Something I have been considering is 'to what extent should I use Names for a hierarchy of Lambda functions in order to modularise the code? I guess that is something you have done with your get_prior function though at first reading I simply read it as a variable name. I think I have some catching up to do. I have even got to decide on the objective; am I looking for the programming equivalent of the combined harvester or will a scythe and a ball of string do the job?
3. This is probably out of scope for SCAN so REDUCE and MAKEARRAY have been used instead (does this imply that the first steps are repeated for every subsequent time period?)
Based on what I've been able to determine, SCAN won't take an array as an init such that an array will be returned of more than one-dimension; I think BYROW/BYCOL/SCAN all have this limitation, but I'm not sure I understand why (other than "that's just how it is"); I've fiddled briefly with some of the "old-fashioned" coercion techniques to get them to produce larger arrays, but with no luck. As for the iterations, I'm assuming that the calcs are only performed for each new row and that the accumulator is just being referenced; I question whether the MAKEARRAY call for each new row is efficient, but I wonder if we'll get a native LAMBDA helper to replicate STACK, which is effectively what its doing here. I wonder if Chris_Gross or some one could provide some insight on what's going on under the hood so we can know better what is efficient or will slow a workbook down once I've got a hundred different calls in one.
Have I got this right, or are there things I have misunderstood even at this stage? >> Sounds right to me!
I have even got to decide on the objective; am I looking for the programming equivalent of the combined harvester or will a scythe and a ball of string do the job >> I think it could be both? I know having simple LAMBDA access could make data-cleaning easy, even more so with the new helpers. I think we fall back to the efficiency argument to determine the scale of the implements needed; will these type of REDUCE/MAKEARRAY combinations bog things down, and how many is too many?
My guess is that this is a positive decision to avoid situations that could potentially give rise to an 'array of arrays'. That is fine if there are active plans to support such data structures natively (we can wait) but, if not, defaulting to a padded 2D array would be a far better solution than throwing an error. Forcing you to use REDUCE / MAKEARRAY to achieve the obvious result is not satisfactory.
It isn't even as if such ideas are alien to Excel.
This reminded me of an issue I took with some LAMBDA formulations I was seeing on various fora that worked with ranges, but wouldn't necessarily work with arrays. The ":" operator here is acting as a VSTACK for ranges, so if it were extended to arrays, we could avoid MAKEARRAY entirely, I think.