3 Replies Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    formula to help manage multiple records?

    Kristin _

      I frequently have tables that I need to draw in from Excel where there is more than one record per person, for instance, and I need to manipulate the data so that I get down to one record per person in Monarch.  This might involve compiling a list of values from all the records for a person into one cell (column), or moving data to calculated columns for a person.  I am a new Monarch user, and am not seeing any possibilities among my formula options.  It would also be helpful if I could draw in data from a summary into a calculated column in a table, and I'm not seeing a way to do that either.  Does anyone know of a way to approach these issues?  Many thanks in advance!

        • formula to help manage multiple records?
          Grant Perkins

          Hi Kristin and welcome!

           

          As a new user Monarch may look a little daunting for a short while until things fit into place. It should not take too long.

           

          You did not indicate whether you have the Standard or Pro version of Monarch  -indeed which version of Monarch do you have. Is it safe to assume version 8?

           

          If so it sounds like it would be good it you also have the Pro version so that you can use the database and external lookup functions. Sounde like that's what you have so I'll go on from there. Be aware that you are jumping right in to the more technical parts of Monarch rather than joining in gently with a few reports first!

           

          If the records you need to combine have a single entry per KEY per table it should be possible to pick one code which all the other tables share and link through that.

           

          But it may not be that simple. Perhaps you don't have any table that has entries for all the master 'key' codes. If that is the cae you may find the best route os to create a starter file from the data of twop or more tables to get a complete list and then use that to link in all the other keys.

           

          Whilst it is a nice way to work you cannot always get the combinations you need using a single pass through the data so would the idea of multiple steps in the process be OK to you? The concept is that you use the ouotput from the first step of the process as the basis (or at least an input) to the next step and so on until you have all you need. Once the steps work they can all be combined to run as a 'single click' process using a batch file to define the process.

           

          It would be much the same using the result of a summary to feed back into another part of the process.

           

          By nature the summary is a volatile calculation whereas the table deals with either constant per record values or constant lookup values (at the point of lookup) or a fixed calculation rule.

           

          However you could export the result of a summary activity then use that as an input value or a lookup for a calculated field in a table, so another two step process.

           

          If that does not make sense can you post some simplified examples (or the real thing if you prefer!) of what you need to do so we can get our heads around it and offer some specific suggestions? If that is possible it may be the best way forward to a quick solution to your requirements.

           

          I hope this helps in some way.

           

          Grant.

          • formula to help manage multiple records?
            Kristin _

            Hi Grant,

             

            I feel so fortunate to have gotten a reply from a Monarch Grand Master!  Thanks for your generosity in sharing your time and expertise (and I'm curious as to how someone becomes a Grand Master if you care to share ).

             

            You deduct correctly - we do have Monarch Pro version 8.

             

            The table I am working with at the moment has one unique key per record, but unfortunately, this key is more specific than how I would like it to be keyed.  For example, I am working with a table that contains details about a person's academic credentials.  I have one record per institution attended per person.  What I need to get to is one record per person.  So if a person attended three different schools, I have 3 records, and want to get to only one.  I have a number representing each person as a field in the table that I could use to tell the system how to find the relevant records.

             

            I have previously handled this kind of situation in Excel by using if statements to compare the person id to the person id in the record below it and if it's the same, to return the selected value.  But this can take some time to setup so I was hoping I could build a model to handle this.

             

            I have one model under my belt for a previous project, and found that I did need to export and import 3 times to get to what I needed (exporting a summary table as you suggest).  I am not opposed to doing this, but am interested in the "single click" process you mention.  Is this through the use of projects?

             

            Many thanks!

            Kristin

            • formula to help manage multiple records?
              Grant Perkins

              Hi Kristin,

               

              Impressed by titles eh? Must make a note of that ...        

               

              If you have a look  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=faq#usertitles"]here[/url] you will see how to obtain one yourself. Or you could try bribing the moderators I suppose. Your choice.        

               

              Sounds like you are already familiar with the multi-stage process concept. Sometimes it is difficult to avoid it when the data are coming from different sources, as seems to be the case here. In such a situation the power of Monarch to be able to 'connect' the data, by manipulation of required, without it getting too messy is a huge benefit. I have sometimes resorted to using queries in Access to combine tables using an SQL query that I then use as the database feed for Monarch - but only because I was using very large files and settnig up the database one-time was process efficient in that case.

               

              The 'single click' option, at it's simplest, is simply to idea to create a batch file to run the process rather than doing each stage one at a time. It's not always appropriate, sometimes outputs are best checked before the next process stage, but should work for your needs here I think. Using projects will make the creration of the batch script much simpler - but it's not too complicated either way. A more sophisticated process could use some VB code (for example) but I am no programmer so I leave that to others.

               

              (Plan C, if the process volumes are high, regular and repetitive would be to look at Data Pump to enhance and automate the Monarch experience.)

               

              If your source files have a common code that relates to each person (or you have a way of translating the person ID code in each file to a common code for a 'master' list, then I would expect it to be possible to link several external tables as database lookups BUT it may not be that simple. For example I had a project which consisted of set of 3 data files (and some internal lookup tables for numeric field value to full description display purposes) which all had a suitable common identifying key set but no single file had all of the records that were required to generate the full data set I needed. So in effect I had to find a way to create a Master File of the possible key combinations from all files before I could do the lookup work. (There were other approaches but that idea seemed to offer the least load on the systems given the fiel sizes and the need to combine multiple years of files for each 'file' AND deal with a few data structure changes along the way.)

               

              I suspect you already have a master "Master" list so just the lookups are required.

               

              If the different sources have different link keys  you have the possibility of using a calculated field  (for each link if necessary) to create the key that a link requires. And in version 8, if my memory is right - I have not so far adapted my past project to look at new features - you can link somewhat more readily than was previously possible through fields pulled in from another link.

               

              Getting comfortable with the files and data structure available and what it needs to come out looking like are often more difficult than geting Monarch to do the work.

               

              Any chance of posting a made up sample that shows, perhaps for a single ID record, what the inputs look like, outlines the problems with the ID field in terms of creating links and shows the key elements of the output required? I'm not concerned about the various detail fields, just the key fields and how they link together.

               

              Whichever way you do it I suspect it will be at least a 2 stage process, probably 3 to make the final consolidation into a single record, especially if there can be a variable number of institutions per person ID.

               

              I hope this helps in some ways for now.

               

               

              Grant

               

              [size="1"][ May 03, 2006, 03:49 PM: Message edited by: Todd Niemi ][/size]