error on formula - CASE WHEN (inventorylocation) = '9006 TK Warehouse' THEN ((locationquantityavailable) /CASE WHEN (purchaseunit) = 'Each' THEN 1WHEN (purchaseunit) LIKE 'Case%' THENCAST(REGEXP_SUBSTR(purchaseunit, '\d+') AS INTEGER) ELSE 0END) END

AI generated answer (using internal Slack threads):

Based on the conversations, it seems like you're trying to create a formula that calculates the available quantity in a specific location ('9006 TK Warehouse') and divides it by the purchase unit. The purchase unit can be 'Each' or 'Case%'. If it's 'Each', you divide by 1. If it's 'Case%', you extract the number from the string and cast it to an integer to use as the divisor. If the purchase unit is neither 'Each' nor 'Case%', you divide by 0. Here's a breakdown of your formula: 1.

CASE WHEN (inventorylocation) = '9006 TK Warehouse'

- This checks if the inventory location is '9006 TK Warehouse'. If it is, the formula proceeds to the next step. If it's not, the formula ends and returns NULL. 2.

THEN ((locationquantityavailable) /CASE WHEN (purchaseunit) = 'Each' THEN 1

- If the inventory location is '9006 TK Warehouse', the formula checks if the purchase unit is 'Each'. If it is, it divides the available quantity in the location by 1. 3.

WHEN (purchaseunit) LIKE 'Case%' THEN CAST(REGEXP_SUBSTR(purchaseunit, '\d+') AS INTEGER)

- If the purchase unit is not 'Each' but starts with 'Case', the formula extracts the number from the string, casts it to an integer, and uses it as the divisor for the available quantity in the location. 4.

ELSE 0 END) END

- If the purchase unit is neither 'Each' nor starts with 'Case', the formula divides the available quantity in the location by 0, which will result in an error because division by zero is undefined in mathematics. If you're getting an error, it could be because of the division by zero. You might want to handle this case differently, perhaps by returning a specific value or a message indicating that the purchase unit is invalid. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******