6 Replies Latest reply: May 15, 2014 10:03 AM by Diane Miller RSS

    Logarithm functions

    Diane Miller

      Does anyone know how to calculate a logarithm?

       

      I am trying to duplicate "inibal=log(1/dsinibal)". (dsinibal is the initial balance in my system)

        • Logarithm functions
          Data Kruncher

          Hi Diane,

           

          Monarch does not offer a built-in log function.

           

          Have you considered exporting the values out to an Excel sheet along with a value that you can use as a lookup key, having Excel do the calculations, then importing the results back into Monarch (if necessary) using the key?

            • Logarithm functions
              Diane Miller

              If I were only going to use this once a month that would be a viable solution. Unfortunately we are talking about doing this daily with 500 to 100,000 records depending on the number of accounts we load in a single day.

               

              Now I bet you will come back & tell me there is a way to automate the process.

               

              If so, please oh please share with me! I love it when I learn something new! I went to the 2007 User Conference & heard discussions of ways to automate some things but had no use for it at the time.

               

              Thanks,

                • Logarithm functions
                  Data Kruncher

                  I'm concerned about the larger data sets. Do you have MS Access installed?

                    • Logarithm functions
                      Diane Miller

                      Yes and that is a good point as I don't have the newest version of Excel which can handle more than 65,000 someodd records in a single tab.

                        • Logarithm functions
                          Data Kruncher

                          Good. I don't use Excel 2007 either. Yuck :p. I'm not keen on it.

                           

                          OK, here's the plan.

                           

                          Fire up the Project Export Wizard. Export your table to a new Access mdb. Setup the export to "Add data to file" when the output file exists. For the Table options, select the "Overwrite existing table" option. Name the table Balances. Run the export.

                           

                          Now start Access and open your new database, which has but a single table.

                           

                          Go to the list of Queries and create a new query using the wizard. Select your key column and the dsinibal field, ignoring all others. You can just select Finish now to close the wizard. Now double click the new query to open it.

                           

                          Here's where we go off the beaten path a little.

                           

                          With your query results displayed, go to the View menu and select SQL view.

                           

                          You'll see something like this:

                          SELECT Balances.Acct_Num, Balances.dsinibal

                          FROM Balances;[/code]Edit this ever so slightly, making it read:

                          SELECT Balances.Acct_Num, Balances.dsinibal, Log(1/Balances.dsinibal)/Log(10) AS LogValue

                          FROM Balances;[/code]Save the changes with Ctrl-S or by clicking the disk icon, and close the query window. You can now run the query to see what it does. So far so good?

                           

                          Now close Access. Your new query is now part of the database.

                           

                          Switch back to Monarch, and build a new external lookup in the Table.

                           

                          Name it Logs, and use the mdb file as the source. Now in the list of datasets, you have not just your original exported table, but your new query as well. Select the query, and proceed with the wizard as you would normally, importing LogValue based on your key.

                           

                          Save the model and the project files.

                           

                          Now your process will be to first run the export, then refresh your external lookups, and you should have new log values every time.

                           

                          If this works as intended, and it should, it's effectively automation without programming.

                           

                          What do you think? Does it suit your needs?