Forum Discussion
Dimension of Array variable inside LET FUNCTION does not match dimension of the Table assigned
I have 4 tables with a different number of rows (1,2,3,4 rows respectively) but when I add the logic highlighted to select which table to assign to the JE_Template variable based on the values of the Statement parameter they all end up stacking the number of rows of the largest table in the if statement (4) as shown below.
Any insights as to what is going on (and a possible solution) will be most appreciated!
I inserted a line with the value of variables for reference of each iteration so you can see the right table is selected but for some reason the number of rows is always expanded to the rows of the larger table (table 4 in this case). Also, in the case of Table 1 which has only 1 row I don't understand how the JE_Template displays 3 rows populated. I also tried to filter based on the content of the JE_Template but functionality was limited and hit/miss.
gfpcp All of the variables used in the JE_Template definition are array objects (UR_Local, MCcy and Statement). To prove this, temporarily use TYPE(UR_Local) or TYPE(MCcy) as the final calculation of your LET statement (in place of DIU_Upload)... the result will be 64 (type: array). As such, INDEX(MCcy,1) will return an array object containing a single value (e.g. {TRUE} instead of TRUE). The default behavior of the IF function when an array object is passed to the logical_test argument is as I described in my first response above.
To force an array containing a single logical value (Boolean) to return TRUE or FALSE (type 4) instead of {TRUE} or {FALSE} (type 64), use the implicit intersection operator (@). For example, try changing the definition of JE_Template as follows:
JE_Template, IF( @INDEX(UR_Local, Statement) < 0, IF(@INDEX(MCcy, Statement), Table_4, Table_2), IF(@INDEX(MCcy, Statement), Table_3, Table_1) )I hope that makes sense. Please see the attached sample workbook, if needed...
6 Replies
gfpcp The resolution of the screenshot provided is too small to read (400x272). It would be best to share the entire formula using the "Insert/Edit code sample" </> button when posting a question or reply.
Based on your description, however, it would appear that you are passing an array object to the logical_test argument of the IF function (or the index_num argument of the CHOOSE function). When the logical_test is an array of values (or an array containing a single value, e.g. {TRUE}), the value_if_true and value_if_false arguments are automatically resized to be the same dimensions. Arguments containing a single value or vector (single row or column) are repeated to fill the same number of rows/columns of the largest array, whereas arguments containing 2D arrays are expanded to fit the same number of rows/columns of the largest array (new rows/columns are filled with #N/A).
For more information, please see: Weird Results from Array Manipulation
Thanks djclements here is the formula:
=LET( IDs, Statements[Allocation ID], StatementNAV, Statements[Ending NAV], LedgerNAV, XLOOKUP(IDs, Ledger[Specific Position ID], Ledger[NAV Local '#EndDate'#], , 0), MCcy, XLOOKUP(IDs, Ledger[Specific Position ID], Ledger[Multicurrency], , 0), Adjustments, XLOOKUP( IDs, Adjustments[Specific Position ID], Adjustments[Valuation Adjustment (local ccy)], 0, 0 ), UR_Local, ROUND(StatementNAV + Adjustments - LedgerNAV, 2), Headers, DIU_Fields[#Headers], DIU_Upload, REDUCE( "REDUCE FUNCTION ISSUES", SEQUENCE(ROWS(IDs)), LAMBDA(DIU, Statement, LET( JE_Template, IF( INDEX(UR_Local, Statement) < 0, IF(INDEX(MCcy, Statement), Table_4, Table_2), IF(INDEX(MCcy, Statement), Table_3, Table_1) ), VSTACK( DIU, HSTACK( "MCcy: " & INDEX(MCcy, Statement), "UR_Local: " & INDEX(UR_Local, Statement), "JE_Template rows: " & ROWS(JE_Template), "", "" ), JE_Template ) ) ) ), DIU_Upload )Apologies for the image, the argument to the if statements are single elements of an array selected with the index faction; and all arrays used are 1-dimentional. The expected return is a bidimensional array (i.e. the table data). Hope this helps.gfpcp All of the variables used in the JE_Template definition are array objects (UR_Local, MCcy and Statement). To prove this, temporarily use TYPE(UR_Local) or TYPE(MCcy) as the final calculation of your LET statement (in place of DIU_Upload)... the result will be 64 (type: array). As such, INDEX(MCcy,1) will return an array object containing a single value (e.g. {TRUE} instead of TRUE). The default behavior of the IF function when an array object is passed to the logical_test argument is as I described in my first response above.
To force an array containing a single logical value (Boolean) to return TRUE or FALSE (type 4) instead of {TRUE} or {FALSE} (type 64), use the implicit intersection operator (@). For example, try changing the definition of JE_Template as follows:
JE_Template, IF( @INDEX(UR_Local, Statement) < 0, IF(@INDEX(MCcy, Statement), Table_4, Table_2), IF(@INDEX(MCcy, Statement), Table_3, Table_1) )I hope that makes sense. Please see the attached sample workbook, if needed...
