Forum Discussion
Lambda To Rearrange Data
I owe you an apology. I rushed the previous formula before taking the dogs out and introduced an error. I think this is correct.
= LAMBDA(Centre,Transaction,[field], LET( k, SEQUENCE(ROWS(TblOriginal)), blockNum?, ISNUMBER(centreSrNo), blockHdrRow, FILTER(k,blockNum?), fld, IF(ISOMITTED(field), SEQUENCE(1,7), field), idx, INDEX(blockHdrRow,Centre), IF(fld<=3, INDEX(Original, idx, fld), INDEX(Original, idx+1+Transaction,fld-2)) ) )(3,4)As SergeiBaklan suggested, PQ is purpose written for this type of data manipulation and should be the first strategy to be considered. What I have tried to do in the attached is to take the restructuring in two steps. The first is to write a Lambda function capable of reading any given data item from the original table, treating it as an array in 3 dimensions (Centre, Unit, Transaction).
To normalise the data from there requires one to build a set of dimension indices corresponding to records within the intended normalised table. 'CentrNo', 'UnitNo' and 'FieldNo' do this but they are not that easy to create for irregular lists.
Something that is possible, having created the first Lambda function, is to omit the step of creating a normalised form of the dataset altogether, and simply work with the original. One loses the flexibility of the pivot table but, in some instances, generating the desired results requires less work than normalising the data.
Another formula of note within the solution is the FillDownλ function used to calculate the 'CentreNo' array
= LAMBDA(values, SCAN(0, values, LAMBDA(prev,curr, IF(ISNUMBER(curr), curr, prev)) ) )As yet, I would not claim to know what constitutes good practice when it comes to using Lambda functions. There is too much 'learning as one goes' for that and, as always, multiple strategies are frequently available..
A key part of the solution is to develop a Lambda function that will return a record given the centre and transaction numbers
= LAMBDA(centre,transaction,
LET(
blkSrNos, INDEX(Original,,1),
blockHdrRow, XMATCH({1;2;3}, blkSrNos),
field, SEQUENCE(1,7),
idx, XLOOKUP(centre, blockHdrRow,blockHdrRow,,1),
IF(field<=3,
INDEX(Original, idx, field),
INDEX(Original, idx+1+transaction,field-2))
)
)(2,2)which can be called using
= NewRecordλ(2,2)
- Thanks a Lot Peter ! MAY God Bless you with Long and Healthy Life !
I have surely become a LAMBDA lover recently and I am sure that this is going to prove a Life long relationship. Thanks to people like you and SergeiBaklan I am never stuck, whenever their is an issue. Let me try to understand the above and apply it to my situation. Should the help be needed, I know where to knock ! ThanksI owe you an apology. I rushed the previous formula before taking the dogs out and introduced an error. I think this is correct.
= LAMBDA(Centre,Transaction,[field], LET( k, SEQUENCE(ROWS(TblOriginal)), blockNum?, ISNUMBER(centreSrNo), blockHdrRow, FILTER(k,blockNum?), fld, IF(ISOMITTED(field), SEQUENCE(1,7), field), idx, INDEX(blockHdrRow,Centre), IF(fld<=3, INDEX(Original, idx, fld), INDEX(Original, idx+1+Transaction,fld-2)) ) )(3,4)As SergeiBaklan suggested, PQ is purpose written for this type of data manipulation and should be the first strategy to be considered. What I have tried to do in the attached is to take the restructuring in two steps. The first is to write a Lambda function capable of reading any given data item from the original table, treating it as an array in 3 dimensions (Centre, Unit, Transaction).
To normalise the data from there requires one to build a set of dimension indices corresponding to records within the intended normalised table. 'CentrNo', 'UnitNo' and 'FieldNo' do this but they are not that easy to create for irregular lists.
Something that is possible, having created the first Lambda function, is to omit the step of creating a normalised form of the dataset altogether, and simply work with the original. One loses the flexibility of the pivot table but, in some instances, generating the desired results requires less work than normalising the data.
Another formula of note within the solution is the FillDownλ function used to calculate the 'CentreNo' array
= LAMBDA(values, SCAN(0, values, LAMBDA(prev,curr, IF(ISNUMBER(curr), curr, prev)) ) )As yet, I would not claim to know what constitutes good practice when it comes to using Lambda functions. There is too much 'learning as one goes' for that and, as always, multiple strategies are frequently available..
- Both the solutions are working perfectly now. I am trying to understand both and apply the same to my projects on a general basis. I assume their is nothing here like "Closing the thread" and I can further post any queries relating to this topic here in the coming days. Thanks a Lot to both of you !
