6 Replies Latest reply: May 15, 2014 9:59 AM by jdslater _ RSS

    Newbie question

    jdslater _

      Is this the correct forum to ask for help with calculated field formula/function capability?

        • Newbie question
          Grant Perkins

          Could be, especially if it relates in any way to Monarch! (Although there are those on her who can help out with similar questions related to Excel and others ...)

           

          Welcome to the forum!

           

          If you are going to talk filters and functions don't for to let us know which version of Monarch you have to play with. It would not help if people suggested solutions using options which are not available in the older versions if you happen to have an older version.

           

          I'm looking forward to the challenge ...

           

           

          Grant

          • Newbie question
            jdslater _

            OK - Thanks Grant.  Using Monarch Pro 8.

             

            Sample data layout (Row is a calc.column Rowno()):

             

            Item #     Sales     Item %    Row

            123456     $1500      4.37%     1

            123457     $1200      3.78%     2

            123458     $1150      3.10%     3

            123459     $  25     (NULL)     4

             

            I would like to be able to use the actual row number at the 1st occurrence of (NULL), 4 in this example, in a formula for another calculated field.  This row number would vary from month to month.

             

            Make sense or do I need more detail/example?

            • Newbie question
              Grant Perkins

              Originally posted by jdslater:

              OK - Thanks Grant.  Using Monarch Pro 8.

               

              Sample data layout (Row is a calc.column Rowno()):

              [font="courier"]Item #     Sales     Item %    Row

              123456     $1500      4.37%     1

              123457     $1200      3.78%     2

              123458     $1150      3.10%     3

              123459     $  25     (NULL)     4[/font][/quote]I would like to be able to use the actual row number at the 1st occurrence of (NULL), 4 in this example, in a formula for another calculated field.  This row number would vary from month to month.

               

              Make sense or do I need more detail/example? /b[/quote]OK.

               

              Is the field to be calculated then to be used just of the lowest numbered line or for all records (or a number of records) from the rest of the table? I get the feeling that is is the latter you are looking for.

              Do you want ROWNO() or RECno() ? (I assume ROW but thought it would be useful to check.)

               

              Assuming the result fo the calc is to be used for all rows in the table I think the process would be something like this.

               

              Define a calcalated field for each record based on

               

              IF(ISNULL(ITEM_FIELD), calculation, "")

               

              From the resulting table create a SUMMARY, filtered  and from that obtain the MIN value of of the Calculated field.

               

              Export that value somewhere to be used in a lookup.

               

              Create another calculated field for the final calculation you mention making use of the exported lookup value as part of that.

               

              I am assuming you need to automate this. If not you could avoid the export and simple enter the value into a user prompted field before the second pass of the data. This could all be handled by one model providing there was an expectation of the second calculated field failing to calculate correctly during the first pass

               

              If that describes the process I am happy to try some experimentation to see if the theory works out!   [img]smile.gif[/img] 

               

              Grant

              Grant

              • Newbie question
                jdslater _

                Sorry for the delay getting back Grant.  It seems like you are right with me here. The way I read your post seems to be saying what I am trying to request/say as well.

                 

                I'll give a further example using the sample data from before by adding the NewCalcField where each new calc cell value would be the ROWno (or RECno if you think its better) divided by the value we found at NULL-1.  Make sense?  Shown below.

                 

                Item # Sales Item % Row____NewCalcField

                123456 $1500 4.37% 1_______ = 1/3

                123457 $1200 3.78% 2_______ = 2/3

                123458 $1150 3.10% 3_______ = 3/3

                123459 $ 25 (NULL) 4

                • Newbie question
                  Grant Perkins

                  Originally posted by jdslater:

                  ... divided by the value we found at NULL-1. 

                  Item # Sales Item % Row____NewCalcField

                  123456 $1500 4.37% 1_______ = 1/3

                  123457 $1200 3.78% 2_______ = 2/3

                  123458 $1150 3.10% 3_______ = 3/3

                  123459 $ 25 (NULL) 4 /b[/quote]I have taken this to mean that you are still dealing with ROWNO() (or RECNO()!) numbers here rather than the ' % values' found in the Item % column or something like that.

                   

                  I am also not sure what you would do with any records AFTER the first NULL - hopefully nothing much or this could get very complicated!

                   

                  Anyway, I think I have a way to work the first 2 examples as I understand them and based on the samples above. It may be that working with the model files would be easier than trying to work out from a written description so send me a PM with an email address and I will happily forward the model and work file.

                   

                  Basically I use a 2 pass process though ultimately this can be run from a batch file as a single click.

                   

                  Pass one reads the report, adds a few calculated work fields and creates a summary with 3 columns (only 2 really used). My report file has spaces rather than NULLS but the idea would be the same.

                   

                  So I use the ISEMPTY() function to flag whether a field has an Item % value or not. (Values will be 0 or 1). Using the same idea I have another field that records the ROWNO() of any record with an EMPTY (NULL) Item % but sets any other numbers to 9999 (or a high value that will move them after the NULL records if sorted).

                   

                  I then created a summary with the keys for these two calc field, in the order presented above, and a MEASURE using ROWNO() with the field calculation set to UNIQUE VALUE.

                   

                  With the first field sorted in descending order (to get the '1's, i.e. the NULL fields to the top) and the second field sorted in Ascending order (to get all the 9999 fields to the bottom) the first UNIQUE value to appear in he measure column will be the ROWNO() of the the first NULL field. Other null field will be grouped together on another line under an 'All Others' reference and all the filled fields will appear on another line also under an 'All Others' reference.

                   

                  We will be exporting this small summary and linking back the table (unless you just want to read the value and put it in an internal lookup table or use it as an input parameter for another session ...

                   

                  We will be linking to the table using that first column (1 or 0) but at the moment we may have more than one row related to NULL records both with '1' values, so we will get the message about a non-unique link. This doesn;t really matter since the first instance will be used anyway, which is what we want. However, if you set the properties of that summary column to "Suppres Dupklicate Values" in the Display properties tab that issue will also be resolved.

                   

                  So, export the summary somewhere (I exported to an Excel file but there are options to that which may be better. Your choice really if you want to improve speed by avoiding the Excel route and its formatting.)

                   

                  Now go back to the table where we will create another calculated field to INVERT the values of the 'IS it NULL' field - so swap 0 to 1 and 1 to 0 using an IF statement.

                   

                  You also need to be careful with the fields n play and the ones that the Excel table (or other exported file type) will be interpreted as having since the lookup process required field compatability 0 so char to char or numeric to numeric. If using CHARACTER fields watch out for field sizes, alignment and other issues. Liberal use of INTRIM() is highly recommended!

                   

                  So, now we have the inverted value fields youo can go back to your table and define an External Lookup to the exported summary.

                   

                  The link will be the first column of the summary (the 1 or 0 flags from the ISNULL() field) to the new INVERTED value field and needs to give you a new field with the UNIQUE ROWNO() from the first line of the summary whch should then appear only against the rows which are NON NULL.

                   

                   

                  It should then be a fairly simple exercise to generate an other calculated field for each row giving Item %/ the UNIQUE ROWNO() constant or UNIQUE ROWNO() CONSTANT -1 or some other combination.

                   

                  It occurs to me that you may want to refine this to add a date/time to the summary and bring that back as well so you know you are dealing with a recent lookup table not an old one!

                   

                  To script the process just define a project export for the summary using the Overwrite option for each export.

                   

                  The batch file needs to open Monarch and call the Project file, open the report and export the summary.

                   

                  Then repeat the process and either leave the file open at your preferred screen for browsing or, perhaps, export the now complete analysis to wherever you want it to go.

                   

                  I suspect this process can be refined and of course the retention of the calculated intermediary fields may not be necessary once the process is fully developed.

                   

                  And a disclaimer ...

                   

                  this seems to work well with the sample offered ( duplicated to another 4 rows as well) but there may be other things in the full report which compromise the approach taken. So no guarantees.  smile.gif[/img] 

                   

                  I hope this makes some sense to anyone reading it ...

                   

                  Grant

                  • Newbie question
                    jdslater _

                    Grant I'll get thru this and try it out - will let you know results.  Thanks.

                    Jack