Forum Discussion

Copper Contributor

Jun 19, 2024

Solved

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...

Formulas and Functions

djclements
Jun 19, 2024
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...
IF_TYPE_Example.xlsx22 KB

gfpcp

Copper Contributor

Jun 19, 2024

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.

djclements

Silver Contributor

Jun 19, 2024

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...

IF_TYPE_Example.xlsx22 KB

gfpcp
Copper Contributor
Jun 20, 2024
Thank you! the @ made the trick, I learned 2 things today: the reason of the behavior (from the "weird results..." post) and how to solve it for the kind of result I need. Thanks again!
- SergeiBaklan
  Diamond Contributor
  Jun 20, 2024
  gfpcp
  
  In addition to great djclements explanation, INDEX(MCcy, 1, 1) here works the same way as @INDEX(MCcy, 1), i.e. returns single value instead of one element array.
  - gfpcp
    Copper Contributor
    Jun 20, 2024
    Thanks! I'll give it a try too.