5 Replies Latest reply: May 15, 2014 10:07 AM by JoeMcG _ RSS

    One branch -two descriptions

    JoeMcG _

      Good Morning!

      My report shows a month of daily activity for a machine. Each machine has a 6 digit ID number (first three positions = branch number); there may be more than one machine per branch.  Each machine also has a text location name.

       

      ScannerID      Branch_num     Location                  SUM     

      011001            011                  52nd-New York             1591     

      011694            011                  INVALID SCANNER ID       386

       

      I'd like to include Location name in my summary by branch, but because fields are unequal, they are treated as unique. Can I sum by branch and just use the first location name?

        • One branch -two descriptions
          Nigel Winton

          Joe

          You could set up a Lookup Field to show the same description for each branch number, then use this in your summary. Depending on how many branches or how often you need to add new ones would decide if you needed an calculated field lookup or an external lookup.

          For a small number that never change make it a calculated field, anything more use an external lookup to an Excel spreadsheet.

           

          Regards

           

          Nigel

          • One branch -two descriptions
            Olly Bond

            Hello Joe,

             

            I'm sure this can be fixed - usually in Monarch there are several ways to fix it, and finding the right one for you might require a little more information.

             

            Is the input data like this:

             

            Scanner     Branch     Location

            123456     001     New York

            123457     001     Manhattan

            123458     002     Las Vegas

            123459     002     Las Vegas, NV[/CODE]

             

            Where you want to end up with:

             

            Branch     Location      Scanner

            001     New York     123456

                           123457

            002     Las Vegas     123458

                           123459[/CODE]

             

            In other words, for each unique branch number, you want a single clean text field for location followed by a listing of the machine IDs at that branch?

             

            Can be done with a calculated field, or external lookup, or two model approach - a full sample would confirm what was best. Do you have Monarch Pro?

             

            Best wishes,

             

            Olly

              • One branch -two descriptions
                JoeMcG _

                The end result is even less detail than in your example Olly. Its really just a sum of activity for each branch. The location name just makes it a little easier to follow. This is Monarch Pro 8.0.

                 

                I certainly could do as an external lookup if that's the best way. I'm pleased to have gotten this far considering how ugly the orginal .txt report is.

                  • One branch -two descriptions
                    Grant Perkins

                    Hi Joe,

                     

                    There may be a possibility that the Name of the branch on a first row is not great for your purpose. If so then the more complete solution would be to go with a lookup, I think. Based on your simple output requirement Nigel's suggest looks very suitable. If the Branch/Location name description rarely changes I would be tempted to go with an internal lookup (Nigel's calculated field option). For that you can create a 2 column Branch code to Location Name in Excel or a text file and then paste the columns as a block into the internal lookup table window. If the data is quite volatile with regular changes maintaining the cross reference elsewhere ready for an external lookup may be better for you. Perhaps Monarch could be employed to automatically go and create a revised x-ref table for each run against the report if you have a good source of the x-ref somewhere already?

                     

                    On the other hand if the Location of each machine should become important it should be possible to produce a summary by machine and include a subtotal by Branch as well.

                     

                    HTH.

                     

                     

                    Grant