7 Replies Latest reply: May 15, 2014 10:04 AM by Grant Perkins RSS

    New to Monarch...

    JAT _

      Help!

      I am new to Monarch and have created some simple models where all the fields are fairly consistent.  But, I have a report now of employee hours where the detail for each record/employee can vary based on what category of time was worked for that pay period.  See example below.  The data of interest is only at the beginning and end of each employee record.  I am thinking this data extract should be straight forward but cannot seem to get it to work.  How do I need to do the template(s) if the categories are changing from person to person?  Also, I want to make sure the data does not repeat to the next employee. 

      THANK YOU!!!

       

      MY DESIRED OUTPUT = Excel Columns of:

        Employee Name

        Dept

        REG 100     

        REG 110     

        OT 500     

        OT 510     

        TP2     

        TP4

       

      Sample Text: 4 records – 1 per employee (looking for employee and hours worked in each category.

       

      Employee 1                          DEPT 1    001                                                                               

      type                                                                               

      ID IN   DEPT           ACTIVITY       OUT    ID IN   DEPT               ACTIVITY       OUT      TOTALS       

      Sun 06/07 Unscheduled                                                                               

      Mon 06/08   349p*U                             1142p                                                  7.38    7.38                       

      Tue 06/09   336p*U                             1216a                                                  8.17   15.55                       

      Wed 06/10  1234p*U                             1234a                                                 11.50   27.05                       

      Thu 06/11 Unscheduled                                                                               

      Fri 06/12 Unscheduled                                                                               

      Sat 06/13 Unscheduled                                                                               

      Sun 06/14 Unscheduled                                                                               

      Mon 06/15   330p*U                             1235a                                                  8.58   35.63                       

      Tue 06/16   352p*U                             1231a                                                  8.15   43.78                       

      Wed 06/17   339p*U                             1248a                                                  8.65   52.43                           

      Thu 06/18   404p*U                             1254a                                                  8.33   60.77                       

      Fri 06/19 Unscheduled                                                                               

      Sat 06/20 Unscheduled                                                                               

      Subtotals for DEPARTMENT  DEPT 1                                                                               

      REG 110:      60.77      TP2:      24.00                                                                               

      -


                                                                                      Employee 2                                DEPT 2    0023                                                                               

      type                                                                    

               ID IN   DEPT                ACTIVITY       OUT    ID IN   DEPT             ACTIVITY       OUT      TOTALS  

      Sun 06/07 Unscheduled                                                                               

      Mon 06/08   327p                               1156p*E                                                8.00    8.00                     

      Tue 06/09   330p                                151a*L                                                9.85   17.85                     

      Wed 06/10   327p                               1200a                                                  8.00   25.85                     

      Thu 06/11   330p                               1200a                                                  8.00   33.85                     

      Fri 06/12   629a*U                              945a                                                  3.27   37.12                     

      Fri 06/12   327p+N                             1201a                                                  8.00   45.12                     

      Sat 06/13 ABSENT                                                                                (8.00)                            

      Sun 06/14  1234p*U                             1036p                                                  9.53   54.65                     

      Mon 06/15   330p                               1159p*E                                                8.00   62.65                     

      Tue 06/16   331p*L                             1200a                                                  8.00   70.65                     

      Wed 06/17 EXCUSED ABSENCE                                                                            (8.00)                            

      Thu 06/18   326p                               1200a                                                  8.00   78.65                     

      Fri 06/19 EXCUSED ABSENCE                                                                            (8.00)                            

      Sat 06/20 ABSENT                                                                                (8.00)                                                                               

      Subtotals for DEPARTMENT  DEPT 2                                                                               

      REG 100:       3.27  REG 110:      70.27   OT 510:       5.12      TP2:      16.00                                                                               

      -


       

      Employee 3                                   DEPT 2    0023                                                                               

      type                                                                    

               ID IN   DEPT             ACTIVITY       OUT    ID IN   DEPT             ACTIVITY       OUT      TOTALS    

      Sun 06/07 Unscheduled                                                                               

      Mon 06/08  1136a*U                              855p                                                  8.82    8.82                     

      Tue 06/09  1128a*U                              944p                                                  9.77   18.58                     

      Wed 06/10  1133a*U                              948p                                                  9.75   28.33                     

      Thu 06/11  1127a*U                              936p                                                  9.65   37.98                     

      Fri 06/12  1136a*U                              937p                                                  9.52   47.50                     

      Sat 06/13   725a*U                              421p                                                  8.43   55.93                     

      Sun 06/14 Unscheduled                                                                               

      Mon 06/15   857a*U                              931p                                                 12.07   68.00                     

      Tue 06/16   857a*U                              857p                                                 11.50   79.50                     

      Wed 06/17   856a*U                              854p                                                 11.47   90.97                     

      Thu 06/18 Unscheduled                                                                               

      Fri 06/19 Unscheduled                                                                               

      Sat 06/20 Unscheduled                                                                               

      Subtotals for DEPARTMENT  DEPT 2                                                                               

      REG 100:      35.03   OT 500:       8.43  REG 110:      40.00   OT 510:       7.50                                                                               

      -


       

      Employee 4                                  DEPT 3    0023                                                                               

      type                                                                    

               ID IN   DEPT             ACTIVITY       OUT    ID IN   DEPT             ACTIVITY       OUT      TOTALS  

      Sun 06/07 ABSENT                                                                                (8.00)                            

      Mon 06/08 EXCUSED ABSENCE                                                                            (8.00)                            

      Tue 06/09  1000a                                630p                                                  8.00    8.00                     

      Wed 06/10   900aE                              500pE                                                7.50   15.50                     

      Thu 06/11 EXCUSED ABSENCE                                                                            (8.00)                            

      Fri 06/12 EXCUSED ABSENCE                                                                            (8.00)                            

      Sat 06/13 ABSENT                                                                                (8.00)                            

      Sun 06/14 ABSENT                                                                                (8.00)                            

      Mon 06/15   956a                                630p                                                  8.00   23.50                     

      Tue 06/16  1000a                                630p                                                  8.00   31.50                     

      Wed 06/17   954a                                637p                                                  8.00   39.50                     

      Thu 06/18 EXCUSED ABSENCE                                                                            (8.00)                            

      Fri 06/19 EXCUSED ABSENCE                                                                            (8.00)                            

      Sat 06/20 ABSENT                                                                                (8.00)                                                                               

      Subtotals for DEPARTMENT  DEPT 3                                                                               

      REG 100:      39.50      TP2:      32.00      TP4:       8.00

        • New to Monarch...
          Grant Perkins

          Hello JAT and welcome to the forum.

           

          Form what you wrote above I'm assuming you only need the fist line of each record and the last line under "Subtotal ...." . Is that correct? Is it also safe to assume that you can readily trap those two sections without any complication?

           

          If so is there any chance that the Subtotal data, in pairs of category and value, might be presented in nice equally sized columns? If so there MAY be some potential for sing the Multi Column region functionality and treating the subtotals as detail with the employee information as an append. Indeed it may be ppssible to trap all the fields in pairs without the MCR and use the results with some calculated fields to deliver the final output.

           

          If they are NOT columnar then I would suggest trapping the entire line and using some advanced 'slice and dice' techniques (again making use of calculated fields, to deliver the results you need. Writing the explanation of 'how to' may take a while so before getting stuck in I would like to know if we need to eliminate the MCR option, which could be eaaire.

           

          What do you think?

           

           

          Grant

          • New to Monarch...
            JAT _

            Hello Grant,

            Yes, I am only looking at the first and last lines in the record.

            I have no issue with a trap on the first line, but the last line is where I need the help.  The last line contains different categories of time worked per employee.  Each employee can have any combination of the 6 categories (e.g. Reg100, Reg, 110, OT500, TP2, etc.).  So, it looks like the MCR may work as it does line up into columns, but where I am confused is how to do a detailed template when the categories change per employee (see sample text in original post) and I want each category as an individual column in the excel output (see desired output below - with the value inside the table).

             

            Employee Name     REG 100     REG 110     OT 500     OT 510     TP2     TP4

            Employee 1                              

            Employee 2                              

            Employee 3                              

            Employee 4                              

             

            I greatly appreciate your help.

            Thanks!

              • New to Monarch...
                Grant Perkins

                Hi Jat,

                 

                Just the 6 data columns maximum after the Employee information column?

                 

                 

                Grant

                  • New to Monarch...
                    Grant Perkins

                    Jat,

                     

                    I don't suppose there is any possibility that the : would always appear in the same column of the possible MCR scenario is there?

                     

                    Actually is there any chance you could re-post the sample (assuming it is a fairly clean facsimile of the real report) using the CODE tags in an attempt to keep the format as it should be. Sorry to ask but this is one of the more critical aspect of attempting to use MCR here.

                     

                    Grant

                      • New to Monarch...
                        Olly Bond

                        Hi Jat, hi Grant,

                         

                        An append template on:

                         

                        Employee 1 DEPT 1 001

                         

                        should get one half of the data.

                         

                        A detail template on a two line sample on:

                         

                        Subtotals for DEPARTMENT DEPT 1

                        REG 110: 60.77 TP2: 24.00

                         

                        Trap on Subtotals and highlight one big field called Blob covering the whole of the second line.

                         

                        Then to slice and dice looking for specific text, I'd use the Extract() function, e.g.:

                         

                        val(Extract(Blob;"REG 100: ";" ")) ^

                         

                        should do the trick for REG 100, for example.

                         

                        HTH,

                         

                        Olly

                          • New to Monarch...
                            JAT _

                            Thanks Olly & Grant for all of your help!!

                             

                            The "blob" and extract function in the detail template gave me exactly what I was looking for.

                              • New to Monarch...
                                Grant Perkins

                                Hi JAT, Olly,

                                 

                                In the absence of an MCR solution (hence the previous question to check whether such might be an option) I was looking at much the same solution but considering the options if the line was not spaced exactly as per the sample (as I indicated previously) since if it was not a simply spaced output the solution would essentially be the same but require somewhat more processing to be sure of getting the values!

                                 

                                For example, if the values are always placed in a fixed horizontal position on the line (say the category id takes 10 character columns and the value amount also takes 10 character columns then the MCR might work but also it would be easy to extract each data alue based on a section of the line. One could then work out which category column the value should be presented in irrespective of where the category was reported on the line.

                                 

                                If the positioning was not consistent with either MCR or Olly's formula one would have to resort to adding some other functions into the formula to make the extraction work reliably. Fortunately there are plenty to choose from and use either singly or in combination.

                                 

                                Grant