6 Replies Latest reply: May 15, 2014 10:12 AM by OddJob _ RSS

    Calculation Field not working

    AlexM _

      In v.11.0.3.11 (32-bit), I don't seem to be seeing the correct results in a calculated field where I'm trying to populate one field based on converting blanks to zero values in two other fields.

       

      For example, if I have the numeric fields:

       

      If(<>0,[Main Discount1],0)

      If(<>0,[Main Discount2],0)

       

      The new calculated field below should return values:

      If(<>0,[Main Discount1],[Main Discount2])

       

      Instead, I'm just getting blanks where values should appear. (Note: It does appear correctly in v.10!). Any thoughts?

       

      Alex

        • Calculation Field not working
          Data Kruncher

          Hi Alex,

           

          If you're using the standalone/single user version of Monarch, you should install [URL="http://www.datawatch.com/_support/downloads_updates.php"]the v11.1 patch[/URL] and then test your problem model again.

           

          HTH,

            • Calculation Field not working
              Data Kruncher

              For fun, maybe try this and see what you get:

               

              If(=0 .Or. IsNull(),0,[Main Discount1])

               

              Any better?

                • Calculation Field not working
                  kbelli _

                  I could just be imagining things, but I think version 11 (even with the newest patch) "forgets" its calculated fields from time to time, populating them entirely with null values.  It can be really frustrating.

                   

                  Refreshing external data seems to cause Monarch to "remember" to calculate its other calculated fields.  I've gone so far as to add an external lookup to models that have critical calculated fields just so that I can refresh the data when this happens.  At the risk of sounding like a complete fool, try this (if you don't have an existing external lookup in that model):

                   

                  Create a numeric calculated field defined simply as 1.

                   

                  Create an excel workbook with the number 1 in cell A1 and the number 2 in cell B1 (or whatever you want in B1), and save this workbook wherever you tend to keep external lookup sources for Monarch.  (I can't remember off the top of my head if Monarch will want headers for those values.  Maybe you'll need "One" in A1, "Two" in B1, 1 in A2, and 2 in B2.  But I think you can do it without headers.  Regardless, you're just matching the calculated field you created a step ago against your Excel workbook and returning anything at all.)

                   

                  Create an external lookup field , linking field to your newly created workbook and returning the number 2 from the second column.

                   

                  If creating this external lookup doesn't immediately cause Monarch to remember its other calculated fields, then try refreshing external data.  If that doesn't do it, then I really must sound like a fool.  In my defense however, it works for me when calculated fields return nothing but null values.

                    • Calculation Field not working
                      Data Kruncher

                      Hi Kris,

                       

                      Of course, I find this a bit frustrating too, as I don't even have v11 yet to test along with you.

                       

                      But the solution, or workaround, such as it is, as you've described is brilliant. Hey, whatever works, until such time as v11 works as intended! (Assuming of course that it doesn't quite work right now in this regard.)

                       

                      Nice work.

                        • Calculation Field not working
                          Scott Eshleman

                          I'm going to put this to use because I've become quite frustrated with version11 and its issue of needing to be prodded to refresh Calculated Fields, Sorts & Filters,

                          but 'til the 11.1 patch, I had too many other issues with the Monarch 11 Pro "upgrade".

                           

                          While the developers had included buttons to refresh External Lookups, Sorts & Filters, they neglected to include a 'Refresh Calculated Fields' tool.

                          I hadn't figured out a consistent means of refreshing so. Thanks for thinking this one through.

                  • Calculation Field not working
                    OddJob _

                    In v.11.0.3.11 (32-bit), I don't seem to be seeing the correct results in a calculated field where I'm trying to populate one field based on converting blanks to zero values in two other fields.

                     

                    For example, if I have the numeric fields:

                     

                    If(<>0,[Main Discount1],0)

                    If(<>0,[Main Discount2],0)

                     

                    The new calculated field below should return values:

                    If(<>0,[Main Discount1],[Main Discount2])

                     

                    Instead, I'm just getting blanks where values should appear. (Note: It does appear correctly in v.10!). Any thoughts?

                     

                    Alex[/QUOTE]

                     

                    Hi Alex

                     

                    I ecountered a similar problem in V11.1 with calculated fields that produced blank results.

                    V11 appears to handle Null values in calculated fields, in a slightly different way from earlier versions.

                    Where a calculated field tries to perform a calculation on a Null value, the result is Null.

                     

                    The easiest way to see this, is to create a csv file in Excel with 3 records as follows:

                    Record       FirstNumber               SecondNumber

                    A       10                              20

                    B                5                              20

                    C (Leave Empty)                         20

                     

                    Open the csv file in Monarch and create a calculated field e.g. If(FirstNumber + 10 = SecondNumber, "TRUE","FALSE")

                     

                    In Earlier versions, Record A would be True, Records B and C would be False.

                     

                    In V11 Record A is True, Record B is False and Record C is Null.

                     

                    A solution to this is to add a Null condition check at the start of the If statement e.g. If(IsNull(FirstNumber)=0 .AND.Firstnumber + 10 = SecondNumber, "TRUE,"FALSE)

                     

                    This checks the field to make sure there's something in there, before performing the calculation.

                    Because of the use of .AND., the field contents have to pass both tests to be True.

                    For a Null entry, because the first test (the Null check) isn't passed, the calculation evaluates to False without needing to process the second half of the condition test. 

                    For an entry that isn't Null, the first test is passed and the second test can be carried out because there is definitely something there to perform the calculation on.

                     

                    In your example, I would try:

                    If(IsNull()=0 .And. <>0,[Main Discount1],0)

                    If(IsNull()=0 .And. <>0,[Main Discount2],0)

                     

                    Bear in mind that IsNull needs to be the first condition test, whether using this with  .AND. or  .OR.

                     

                    I'm awaiting a reply from Monarch support as to, whether this is an intended feature of V11.

                     

                    Either way, it's probably a good discipline to include the Null check and it should still work, even if future versions of the Software revert to the earlier behaviour.

                     

                    I'm not sure whether this is directly related to your problem but, hopefully it will be of use to you.

                     

                    Edit: I've been informed that second level support have reported this as a bug so this behaviour might change in the next release.