9 Replies Latest reply: May 15, 2014 10:06 AM by svandyk _ RSS

    Concatenate data for similar lines

    svandyk _

      Hi,

       

      I'm a newbie with v10.5.  We got Monarch to extract data from PDF timesheet files.  There is other data on the report but the data I'm concerned with is currently listed like this with one employee per page (the columns line up in the report):

       

      Employee:     lname, fname

       

      Date              In Punch        Out Punch

      2/1/10             8:00 AM       11:00 AM

      2/1/10           11:30 AM         6:00 PM

      2/2/10             4:56 AM         8:00 AM

      2/2/10             8:30 AM        11:00 AM

      2/2/10           11:30 AM         1:30 PM  

       

      What I want to end up with is the format below with a line for each employee/date combination that lists all punches for each day across the page.  An Employee can have 1 to 3 lines of punches per day.

       

      fname    lname    2/1/10    8:00 AM    11:00 AM    11:30 AM    6:00 PM

      fname    lname    2/2/10    4:56 AM     8:00 AM      8:30 AM   11:00 AM    11:30 AM    1:30 PM

       

      I tried the logic in the Join fields together from multiple lines.  I can get the first step but can't get unique lines of fname lname and date with multiple lines of punches below.  My summary also came out with just 2 columns of punches and the values were null.  Please help...

        • Concatenate data for similar lines
          Data Kruncher

          I made up an employee name to append, as follows:

          Name: Test Employee

           

          Date   In Punch Out Punch

          2/1/10  8:00 AM 11:00 AM

          2/1/10 11:30 AM  6:00 PM

          2/2/10  4:56 AM  8:00 AM

          2/2/10  8:30 AM 11:00 AM

          2/2/10 11:30 AM  1:30 PM /code

           

          This tight collection presents a bit of a challenge in telling Monarch how to stop. To the point where I could not determine how to go about converting this in one pass (one model) to the format that you want. It just needs a bit more room to breathe, as it were, I believe.

           

          To that end, I developed a two model solution.

           

          First, a single row detail template captures the RptDate, TimeIn and TimeOut fields. Add an append to pick up the employee name.

           

          Now build a summary. Use the Employee as the first key (down) field. Add RptDate and set it to suppress duplicate values. Now add TimeIn and TimeOut. No measure fields required.

           

          Build a project export to output the summary to formatted text file. Save the model and the project files.

           

          Now in another Monarch session, open the new text file as a report. Build a single line template, trapping on the date field, painting the Employee, RptDate, TimeIn and TimeOut.

           

          The spaces for multiple row dates allow for handling the mutiple times within a single record much more easily. Without the gaps, Monarch really wants to capture your simple list with each line as its own record, making your intended final layout all but impossible.

           

          For TimeIn and TimeOut, go to the Advanced tab of each field definition and set the End Field On to None of the above. This will cause Monarch to capture multiple lines in a single field.

           

          OK, that's it for the templates. On to the Table window.

           

          You should see that TimeIn and TimeOut have all of the related time values for each date. Now we can separate those time values into individual fields.

           

          Create TimeIn1 with:

          TextLine(TimeIn,1)[/CODE]

           

          Duplicate TimeIn1 to make TimeIn2 with

          TextLine(TimeIn,2)[/CODE]

           

          and of course TimeIn3 is:

          TextLine(TimeIn,3)[/CODE]

           

          Do the same for TimeOut1, TimeOut2, and TimeOut3. Finally, hide the now redundant TimeIn and TimeOut fields, and resequence your fields as necessary.

           

          Save the model and the project with new names.

           

          HTH,

          Kruncher

          • Concatenate data for similar lines
            elginreigner _

            Very nicely done Data Kruncher, I'm assuming its a text data type for the time in/out fields in the 2nd model/project?

             

            Svandyk, keep in mind this will only work for a preset number of punches. In you example you have 3 times in/out. If a user would go beyond that your results could be wrong. I would recommend going beyond 3 textlines, preferably 6. I see punch data all day long and I can tell you people go in/out more than they should.

              • Concatenate data for similar lines
                Data Kruncher

                Thanks.

                 

                Yes, I did leave it as Character for the purposes of the post, but the fields could easily be converted to Date/Time.

                  • Concatenate data for similar lines
                    elginreigner _

                    Can you tell I'm a little bored today?

                     

                    I was just walking through the setup. Good job.

                      • Concatenate data for similar lines
                        Data Kruncher

                         

                         

                        It's nice to have somebody following up with things, asking questions and challenging assertions.

                          • Concatenate data for similar lines
                            svandyk _

                            Thanks for the step by step for creating the layout I requested.  I ran into  some issues I need help with.

                            Project Export: Your instruction says to "build a project export to output the summary."  What is the purpose of building a PROJECT export?  I have found that when I created it and saved the project, after I close out and reopen the report and model, the project is not there to open and I need to recreate it each time.  How can I get the project to save so I can re-use?

                            Summary: I need the records to remain in the order they are in on the original report.  The summary option forces each key and item to sort either ascending or descending.  How can I turn the sorting off?  I did check the no sort parameter and it is off.  If I can't turn the sorting off, please suggest a way to add a counter field or some other method so i can prevent the data from sorting.

                            Command Lines: The processing I need for this file requires 3 different models.  Can I use the command line to process the first model and initial report, output the text file and then go on to do the same for the second and third models without user intervention?

                             

                            Thanks for your help

                              • Concatenate data for similar lines
                                Data Kruncher

                                Project Export: Your instruction says to "build a project export to  output the summary."  What is the purpose of building a PROJECT export? /quoteWhen you build a project export (4th icon in the toolbar), as opposed to a "regular" export (3rd icon), you can save a "project" file (.xprj extension) with Ctrl-J. This saves the customized export process(es) that you create, specifying, essential, what goes where. Just as saving a model saves us from recreating the templates every time we want to run it against a new report, saving a project file lets us reuse a defined set of exports.

                                 

                                The project file also associates a particular data source, or data sources, with a specific model. When you open a project file, the report file(s) open automatically, as does the required model.

                                 

                                  I have found that when I created it and saved the project, after I  close out and reopen the report and model, the project is not there to  open and I need to recreate it each time.  How can I get the project to  save so I can re-use?[/quote]Once your model is saved, and you've defined some exporting that you would want to repeat in the future, press Ctrl-J or click the 2nd toolbar icon to save your project file.

                                 

                                Summary: I need the records to remain in the order they are in on the  original report.  The summary option forces each key and item to sort  either ascending or descending.  How can I turn the sorting off?  I did  check the no sort parameter and it is off.  If I can't turn the sorting  off, please suggest a way to add a counter field or some other method so  i can prevent the data from sorting.[/quote]There's no way to turn sorting off for key fields in a summary, so we need another way to handle this. One approach would be to create a new Formula-based calculated field in the Table window to ensure that you get the sequence right. Create a RecNo numeric field with the formula:

                                RecNo()[/code]This is the record number, a sequential number that reflects what Monarch found in the report.

                                 

                                Now go the summary and add the RecNo field as the first key field. Be sure to go to its Display tab and check the Hide the key column box. This will keep your original sequence, but you won't see the sequence number in the summary.

                                 

                                Command Lines: The processing I need for this file requires 3 different  models.  Can I use the command line to process the first model and  initial report, output the text file and then go on to do the same for  the second and third models without user intervention?[/quote]Yes, absolutely. Just be sure to use the DOS START command with its /WAIT parameter to allow each Monarch process to finish properly before the next one fires up.

                                 

                                START "Pass1" /WAIT Monarch.exe /prj:"C:\Folder\Project 1.xprj" /pxall

                                START "Pass2" /WAIT Monarch.exe /prj:"C:\Folder\Project 2.xprj" /pxall

                                Monarch.exe /prj:"C:\Folder\Project 3.xprj" /pxall

                                /codeNo need to START /WAIT the last one in the batch file.

                                 

                                Does this help you progress with your task?

                                • Concatenate data for similar lines
                                  Grant Perkins

                                  Thanks for the step by step for creating the layout I requested. I ran into some issues I need help with.

                                  Project Export: Your instruction says to "build a project export to output the summary." What is the purpose of building a PROJECT export? I have found that when I created it and saved the project, after I close out and reopen the report and model, the project is not there to open and I need to recreate it each time. How can I get the project to save so I can re-use?

                                  Summary: I need the records to remain in the order they are in on the original report. The summary option forces each key and item to sort either ascending or descending. How can I turn the sorting off? I did check the no sort parameter and it is off. If I can't turn the sorting off, please suggest a way to add a counter field or some other method so i can prevent the data from sorting.

                                  Command Lines: The processing I need for this file requires 3 different models. Can I use the command line to process the first model and initial report, output the text file and then go on to do the same for the second and third models without user intervention?

                                   

                                  Thanks for your help[/quote]

                                   

                                  Project Export:

                                   

                                  Open the Project when you want to re-use it, not the report and model separately. The project will open the report and model at the same time. If you need to open a different version of the report you can open a new report once the session is running.

                                   

                                  Summary:

                                   

                                  What is the order of the report? You can make the summary sort in the same order even if the you hide the field from display. However this may make the summary facility less powerful than it could be.

                                   

                                  Without complate visibility of the requirement it is a little difficult to know how to answer the question.

                                   

                                  Command Line:

                                   

                                  Yes you can. Again a bit difficult to make sense of a detailed reply absent knowledge of the entire process

                                   

                                  HTH.

                                   

                                   

                                  Grant