Get Inspire insights from former attendees in our AMA discussion thread on Inspire Buzz. ACEs and other community members are on call all week to answer!
alteryx Community

Alteryx Designer Desktop Discussions

SOLVED

Multi Row Formula Parsee Error

8 - Asteroid

Greetings

I'm trying to allocate available stock based on the earliest requirement date until the unrestricted stock is 0 then noting how much we are short

However I'm getting a Parsee error

This is what I'm aiming to get

Order             Material       Required Date       Required Qty    Todays Stock     Stock Remain      Note

0020018728    10097-0100   20220522              30.000                     51.000                21
0020020708    10097-0100   20220526                1.000                     51.000                20
0020020739    10097-0100   20220527              24.000                     51.000                  0                 Short 4
0020005591    10097-2100   20220407                 1.000                    51.000                  0                 Short 1
0020005591    10097-2100   20220407                 1.000                    51.000                  0                 Short 1
0020005591    10097-2100   20220407                 1.000                    51.000                  0                 Short 1
0020005591     10097-2100  20220407                  1.000                    51.000                  0                 Short 1

Attached is my attempt at the multi row formula and the excel with the data.

Am I using the right tool? or should I be considering a reiterative macro?

4 REPLIES 4
17 - Castor

Hey @BAvitia88,

Haven't checked the logic yet put the parse issue was just due to missing the "IF" at the beginning:

``IF tonumber([Stock remain/short])>0 and  tonumber([ReqQty])<tonumber([Row-1:Stock remain/short]) Then "available" else "short" endif``

20 - Arcturus

@BAvitia88 as @IraWatt mentioned the multi-row formula tool missing the IF statement.

8 - Asteroid

Thank you

However it is not decrementing the stock remaining/short so its classifying as available when we are out

Order                 Material           date           Req Qty       Available today        remain

0020018728      10097-0100    20220522 30.000            51.000                       21       Available  we need 30 have 51 remain 21
0020020708      10097-0100   20220526 1.000              51.000                        20      Available   we need 1 Have 21 remain 20
0020020739      10097-0100   20220527 24.000            51.000                        0        Short 4     we need 24 have 20 short 4

8 - Asteroid

Found the solution:

Created A Record # field used the multi row formula to group and number rows used this statement in a second multi row formula

IF tonumber([Unrest Stock])>0 and tonumber([Record #])=1 and tonumber([ReqQty])<=tonumber([Unrest Stock]) then tonumber([Unrest Stock])-tonumber([ReqQty]) Elseif tonumber([Unrest Stock])>0 and tonumber([Record #])>1
and tonumber([ReqQty])<=tonumber([Unrest Stock]) Then tonumber([Row-1:Remain]) -tonumber([ReqQty]) else "" endif

IF !IsNull([Unrest Stock]) and [Remain]<'0' then "short"+" "+[Remain] elseif IsNull([Unrest Stock]) then "short"+" "+toString([ReqQty]) else "Available" endif

And it worked

Order                     Material        Req Date       Required   Stock     Row     Remain      Short/Available

0020018728      10097-0100      20220522      30.000      51.000     1           21      Available
0020020708      10097-0100      20220526      1.000        51.000     2           20      Available
0020020739      10097-0100      20220527      24.000      51.000     3           -4      short -4

Labels