10 Replies Latest reply: May 15, 2014 9:51 AM by Grant Perkins RSS

    payroll register xml problems

    mariel _

      I'm having problems capturing the data that I need from an xml.  It is basically a payroll register with varying amounts of data per employee.  They can have multiple types of earnings (sick, OT etc..), have multiple state taxes taken out etc...  I'm definitely struggling with the vary length of data between the <employee> tags.  I have tried multi-line traps to no avail, played with the advanced properties, including START FIELD ON preceding string.

       

      I'm a newbie with Monarch and am hoping that someone can point me in the right direction.

       

      Thanks,

      Marie

                                       [font="courier"]            <Employee>                                                                               

      <Employee_ID>2</Employee_ID>                                                                               

      <SocialSecurityNumber>145787878</SocialSecurityNumber>                                                                               

      <EmployeeName>TEST                          ,EMPL2                         </EmployeeName>                                  

                     <EmployeeAddress>                                                                               

      <Address>10 MAIN             </Address>                                                                               

      <Address2>                    </Address2>                                                                               

      <AddressCity>DAYTON         </AddressCity>                                                                               

      <AddressState>AL</AddressState>                                                                               

      <AddressZipCode5>98888</AddressZipCode5>                                                                               

      <AddressZipCode4>    </AddressZipCode4>                                                                               

      </EmployeeAddress>                                                                               

      <EmployeeStatus>A</EmployeeStatus>                                                                               

      <HomeDepartment>  </HomeDepartment>                                                                               

      <LocationCode>99</LocationCode>                                                                               

      <BirthDate>08/08/1977</BirthDate>                                                                               

      <HireDate>12/04/1999</HireDate>                                                                               

      <Employee_Pay>                                                                               

      <Distribution>                                                                               

      <CheckNumber>323</CheckNumber>                                                                               

      <DepartmentWorked>  </DepartmentWorked>                                                                               

      <Net>346.19</Net>                                                                               

      <HoursAndEarnings/>                                                                               

      <OtherHoursAndEarnings>                                                                               

      <HoursEarningsCode>01</HoursEarningsCode>                                                                               

      <HoursAmount>40</HoursAmount>                                                                               

      <EarningsAmount>400</EarningsAmount>                                                                               

      </OtherHoursAndEarnings>                                                                               

      <Taxes>                                                                               

      <EmployeeFederalTaxAmount>12.69</EmployeeFederalTaxAmount>                                                                               

      <EmployeeFICATaxAmount>30.6</EmployeeFICATaxAmount>                                                                               

      <EmployerFICATaxAmount>30.6</EmployerFICATaxAmount>                                                                               

      <EmployerFUTATaxAmount>3.2</EmployerFUTATaxAmount>                                                                               

      <EmployerFUTATaxableAmount>400</EmployerFUTATaxableAmount>                                                                               

      <EmployerFUTASubjectWage>400</EmployerFUTASubjectWage>                                                                               

      <EmployeeStateTax>                                                                               

      <EmployeeStateTaxCode>AL</EmployeeStateTaxCode>                                                                               

      <EmployeeStateTaxAmount>10.52</EmployeeStateTaxAmount>                                                                               

      </EmployeeStateTax>                                                                               

      <EmployerSUITax>                                                                               

      <EmployerSUITaxCode>AL</EmployerSUITaxCode>                                                                               

      <EmployerSUITaxAmount>4</EmployerSUITaxAmount>                                                                               

      </EmployerSUITax>                                                                               

      </Taxes>                                                                               

      <Memos>                                                                               

      <MemoCode>WH</MemoCode>                                                                               

      <MemoAmount>40</MemoAmount>                                                                               

      </Memos>                                                                               

      </Distribution>                                                                               

      </Employee_Pay>                                                                               

      </Employee>                                                                               

      <Employee>                                                                               

      <Employee_ID>3</Employee_ID>                                                                               

      <SocialSecurityNumber>456454563</SocialSecurityNumber>                                                                               

      <EmployeeName>EMP                           ,3                             </EmployeeName>                                  

                     <EmployeeAddress>                                                                               

      <Address>9 DAY               </Address>                                                                               

      <Address2>                    </Address2>                                                                               

      <AddressCity>DAY            </AddressCity>                                                                               

      <AddressState>NJ</AddressState>                                                                               

      <AddressZipCode5>87654</AddressZipCode5>                                                                               

      <AddressZipCode4>    </AddressZipCode4>                                                                               

      </EmployeeAddress>                                                                               

      <EmployeeStatus>A</EmployeeStatus>                                                                               

      <HomeDepartment>  </HomeDepartment>                                                                               

      <LocationCode>01</LocationCode>                                                                               

      <BirthDate>01/01/1944</BirthDate>                                                                               

      <HireDate>01/01/2003</HireDate>                                                                               

      <Employee_Pay>                                                                               

      <Distribution>                                                                               

      <CheckNumber>318</CheckNumber>                                                                               

      <DepartmentWorked>  </DepartmentWorked>                                                                               

      <Net>917.15</Net>                                                                               

      <HoursAndEarnings/>                                                                               

      <OtherHoursAndEarnings>                                                                               

      <HoursEarningsCode>01</HoursEarningsCode>                                                                               

      <HoursAmount>40</HoursAmount>                                                                               

      <EarningsAmount>400</EarningsAmount>                                                                               

      </OtherHoursAndEarnings>                                                                               

      <OtherHoursAndEarnings>                                                                               

      <HoursEarningsCode>07</HoursEarningsCode>                                                                               

      <HoursAmount>0</HoursAmount>                                                                               

      <EarningsAmount>1000</EarningsAmount>                                                                               

      </OtherHoursAndEarnings>                                                                               

      <Taxes>                                                                               

      <EmployeeFederalTaxAmount>262.13</EmployeeFederalTaxAmount>                                                                               

      <EmployeeFICATaxAmount>107.1</EmployeeFICATaxAmount>                                                                               

      <EmployerFICATaxAmount>107.1</EmployerFICATaxAmount>                                                                               

      <EmployerFUTASubjectWage>1400</EmployerFUTASubjectWage>                                                                               

      <EmployeeStateTax>                                                                               

      <EmployeeStateTaxCode>OR</EmployeeStateTaxCode>                                                                               

      <EmployeeStateTaxAmount>113.62</EmployeeStateTaxAmount>                                                                               

      </EmployeeStateTax>                                                                               

      <EmployerSUITax>                                                                               

      <EmployerSUITaxCode>OR</EmployerSUITaxCode>                                                                               

      <EmployerSUITaxAmount>38.11</EmployerSUITaxAmount>                                                                               

      </EmployerSUITax>                                                                               

      </Taxes>                                                                               

      <Memos>                                                                               

      <MemoCode>WH</MemoCode>                                                                               

      <MemoAmount>40</MemoAmount>                                                                               

      </Memos>                                                                               

      </Distribution>                                                                               

      </Employee_Pay>                                                                               

      </Employee>                                                                               

      /font[/quote]

        • payroll register xml problems
          Grant Perkins

          Hi Marie and welcome.

           

          What is it with you newbies these days - diving right in for some of the more complex and advanced possibilities that Monarch can offer?       There is nothing like jumping in at the deep end I suppose but you are setting some serious challenges. I preferred to old days when people could spend the first 3 or 4 months on their first model and then get to complicated things like filters sometime in year 2 ...    

           

          OK, here's a suggestion. I think you were on the right lines with the START FIELD ON PRECEDING STRING approach but there are some constraints and some requirements, due to the end of field markers on the variable size fields (fixed size field, if any, will be OK), to do some secondary processing within the model.

           

          So, the template.

           

          As you have a variable numbero of lines per Employee record you first need to establish the LOWEST number of lines possible for any possible record. This will be the number of lines you need to select for the template sample. In this case, based on your posted sample, the 'record' will start with  <employee>[/quote]and end with  </employee>[/quote]. Include both of those lines in the sample.

           

          With your sample lines loaded create a trap using "<employee>" on the trap line. (I did notice that in the posted sample there was a 1 character shift of this line. I assume that was an editing issue with preparation for posting, but if not and it is a genuine problem make the trap a 'floating' trap.)

           

          Now you need to define the field positions. Don't worry about which row the field is on - ANY row will do providing the PRECEDING STRING you will use is unique within the record lines. The horozontal position of the start of the fields is the important thing here.

           

          I'll use a few sample lines from the start of the record ...

           

          <Employee>                                                                               

          <Employee_ID>2</Employee_ID>                                                                                <SocialSecurityNumber>145787878</SocialSecurityNumber>                                                                                <EmployeeName>TEST                          ,EMPL2                         </EmployeeName>                                                  <EmployeeAddress>

                                                                                          <Address>10 MAIN             </Address>                                         /quoteThe first line is a spare line containing no data field to be extracted. If your shortest record leaves you short of real lines in which to define fields you can use this line (and any similar otherwise unused lines) to define any additional fields which may be required for some records but do not appear in your sample record. Actually what is in your sample does not matter at all other than for later understanding if the model needs modifying and helping with the field positions.

           

          On the second line you will need to paint a field starting where the "2" is and useng the preceding string "Employee_ID" or whatever you choose to use of that string that will make the selection unique. (If it is not unique only the first line with that string in it to the left of the field will be captured.)

           

          Now, that field looks like it will often be greater than 1 character width, so paint is as wide as it needs to be for the longest possible ID code. Don't worry that it will pick up the xml and of data field marker, we will strip that out with a calculated field later.

           

          Do the same with the next line but using the sting "socialsecuritynumber". I will guess that that may be a field with a fixed number of characters so we may not need to post-process it at all.

           

          Repeat as required for employeename.

           

          employeeaddress looks like another 'spare' line with no data. "Address"  looks like it may be fixed width but I can't be sure. If it is not just make it as wide as it could possibly be from the source system.

           

          And so on for every line. Get the horizontal character position of the start of each field right and the rest will fall into place in the table.

           

          So what about the fields that are too long?

           

          Where there is a problem simply create a calculated field to use instead of the original.  There are various functions you can use in a formula for string manipulation but it looks like the most logical approach will be to make the calculated fields as wide as they may ever need to be and use a formula that SPLITs the field and dumps the end of field xml tag. The Monarch LSPLIT function seems to be the most likely function to use. Simply split the field data using the  "<"  as the split point character and retain the first part of the field and that SHOULD be all you need, based on the sample data posted. I just hope that none of the fields you need to extract have  <[/quote]anywhere in the real data!

           

          It does require a bit of work to set up the model but after that, unless the xml format changes, the process should be simple to operate.

           

          So long as you get the horizontal field positions and the preceding strings right the vertical position (the row with the data for the field) does not matter at all. Likewise if some record sets are longer than others that too will make no difference to the extraction of the required fields.

           

           

          I hope this helps and that I have not missed anything too important.

           

          Let us know how you get on.

           

          Grant

          • payroll register xml problems
            mariel _

            Thanks Grant!

             

            It sounds so simple now. I was on the right track but just couldn't finish it off.

             

            There are 2 more concerns that I have,

            1.  My smallest employee may not have enough lines for my largest employee.  Is there any workaround to this, or do I just have to live with that?

             

            2.  Below is a piece of the xml data that I have trapped.  Where I am running into problems is that when employees have multiple earning codes it is only picking up the first <hoursearningcode> not the second.  Such situations will frequently occur when employees have time off, work OT etc.  The same thing can happen if an employee is being taxed in multiple states.  What I'm trying to capture is all the info between the employee tags not just the first occurrence of an element.

             

            [font="courier"]            <Employee_Pay>                                                                               

            <Distribution>                                                                               

            <CheckNumber>1069</CheckNumber>                                                                               

            <DepartmentWorked>  </DepartmentWorked>                                                                               

            <Net>423.17</Net>                                                                               

            <HoursAndEarnings/>                                                                               

            <OtherHoursAndEarnings>                                                                               

            <HoursEarningsCode>01</HoursEarningsCode>                                                                               

            <HoursAmount>40</HoursAmount>                                                                               

            <EarningsAmount>4</EarningsAmount>                                                                               

            </OtherHoursAndEarnings>                                                                               

            <OtherHoursAndEarnings>                                                                               

            <HoursEarningsCode>02</HoursEarningsCode>                                                                               

            <HoursAmount>-3153.53</HoursAmount>                                                                               

            <EarningsAmount>-473.03</EarningsAmount>                                                                               

            </OtherHoursAndEarnings>                                                                               

            <Taxes>                                                                               

            <EmployeeFederalTaxAmount>-186.26</EmployeeFederalTaxAmount>                                                    

                                  <EmployeeFICATaxAmount>-469.03</EmployeeFICATaxAmount>                                                                               

            <EmployerFICATaxAmount>-469.03</EmployerFICATaxAmount>                                                                               

            <EmployerFUTATaxAmount>-12.08</EmployerFUTATaxAmount>                                                                               

            <EmployerFUTATaxableAmount>-469.03</EmployerFUTATaxableAmount>                                                  

                                  <EmployerFUTASubjectWage>-469.03</EmployerFUTASubjectWage>                                                      

                                  <EmployeeStateTax>                                                                               

            <EmployeeStateTaxCode>CA</EmployeeStateTaxCode>                                                                               

            <EmployeeStateTaxAmount>-54.03</EmployeeStateTaxAmount>                                                     

                                  </EmployeeStateTax>                                                                               

            /font[/quote]I created an append template to capture the <hoursearningscode> again and what results is if there is a second instance of this field it will return the 2nd value, if there is no 2nd instance it will return the 1st value.  This will lead to duplicate entries of the 1st earning code.  To solve this i used the following code ;

            [font="courier"]If(=[<hoursearningscode>]," ",[<hoursearningscode2>])  /font[/quote]and for the additional earnings amounts

            [font="courier"] If(=[<hoursearningscode>]," ",[<EarningsAmount2>]) /font[/quote]This seems to work.

            Any flaws in the above code and or a better way of handling it?  Not sure if the append template is the best way to go.

             

            Thanks again,

            Marie

             

            [size="1"][ June 27, 2006, 12:16 PM: Message edited by: mariel ][/size]

            • payroll register xml problems
              Grant Perkins

              Hi Marie,

               

              Hmm, thought you may have a few records which were more complex ...

               

              For consistency and certainty I think we need to look at a slightly more complex solution. 

               

              I think you need to look for the lowest level of data as your DETAIL template.

               

              In this case that seems to be the OtherHoursAndEarnings sections.

               

              The rest of the record needs to be an APPEND template or templates OR APPENDS and FOOTER(s) in combination.

               

              However if you might have multiple sections for TAXES as well, as it seems you might, things could get even more interesting.

               

              A couple of ideas come to mind.

               

              Firstly you could consider a 2 or 3 stage process whereby you extract the records for multiple taxes and/or multiple earningscodes first and save them as files to be used as lookups in the final part of the procedure.

               

              Second idea - you might be able to work out a way to capture each of the problem 'blocks' as a multi-line field within a single pass extraction and then process the result into its component parts. I have no idea which might be the most appropriate approach as that decision will be very much data driven. (Or you could get the problem employees dismissed I suppose but that seems a little extreme ...      )

               

              I think your current solution may fall foul of some of the rules - about appends STARTING BEFORE detail - in some situations and, without taking some time to play around with the sample data, I would be concerned about the double problem and the ability to deal with it consistently in a single pass. It may be possible but ...

               

              I don;t think I will have sufficient time to experiment for the rest of this week. How much time do you have to your targeted completion of the task?

               

               

              Grant

              • payroll register xml problems
                mariel _

                Hi Grant,

                 

                Thanks for the information.  I plan on running the current model on as many xmls that I can get my hands on to see how well it holds up for the sections that have multiples.  Not only can there be multiple OtherHoursAndEarnings and TAXES(state and city), but MEMOS and DEDUCTIONS also.

                 

                Lookups would be new to me, so I would have to investigate them further.

                 

                Not quite sure what you mean by the second idea. If you're talking about capturing the whole block as a memo field and then parsing it out, then I can see that being OK if not a bit cumbersome.

                 

                I think you're best idea was the last one.     

                 

                I have been given a lot of leeway on the completion of this task so I would greatly appreciate you taking a look at this when you have time. 

                 

                I'll keep working away here to get a model to have the most coverage and improve upon from there.

                 

                Thanks for all you're help

                 

                Marie

                • payroll register xml problems
                  Grant Perkins

                  Hi Marie,

                   

                  I thinking you have correctly tuned in to my thinking here.

                   

                  BTW I forgot to include a response about the smallest vs largest record. I think it may be in the main answered by the multiple occurrences discussed subsequently - possibility of too many lines to do anything clever anyway, but the answer would have been that there are certain lines in the records which are no more than xml section headers. You can consider these as spare lines and drop fields into them so long as you have a preceding string to look for somewhere in the record line set. Which line does not matter at all. So some variation in ;actie' lines can be catered for. However I think your requirement goes outside that potential anyway.

                   

                  Lookups are quite easy. I think you will need external lookups so I assume you have the PRO version of Monarch  - but which version level is it? 8 would be good ...

                   

                  If we go that route then any data that may contain multiple records needs to be extracted to its own 'table' (flat ascii file, excel, access, whatever) complete with a key - probably emloyee ID - to link it back with. Some thought will be required to ensure it is all linked back correctly given the variation of the composition potential for a record.

                   

                  One important question not yet covered is ...

                   

                  What does the intended output need to look like?

                   

                  Grant

                   

                   

                  And finally, yes, I an thinking of parsing out the data if you go the multi line route and it can indeed be a bit cumbersome especially if you need to allow for an as yet unconfirmed possible number of lines. However Monarch does offer some pretty powerful functions to help and normally the task only has to be done once. Fingers crossed then.

                  • payroll register xml problems
                    mariel _

                    Grant,

                     

                    I have version 8.0 Pro. 

                     

                    The output doesn't really matter because I can always massage the data to get it to display how I want.  The main concern is that I output everything between the employee tags.  One row per info between tags.  I'd like to say one row per employee but there are cases where an employee gets multiple checks for one payroll run, which would lead to multiple sections between employee tags for the same employee. Using employee id as the key would not work.

                     

                    Sample of output...

                    ee   ss#    addr    addr2  zip   ck    net   etc

                     

                    I haven't yet mentioned that I also need to pull the Company level info out of the XML.  I'm trying to attack one issue at a time.

                     

                    Do you need or would you like a complete XML from me?

                     

                    Thanks again,

                     

                    Marie

                    • payroll register xml problems
                      Data Kruncher

                      Marie,

                       

                      I may have an alternate solution for your challenge with XML files, depending on the software you have available.

                       

                      Please send me a private message if you'd like to discuss.

                       

                      Regards,

                      Kruncher

                      • payroll register xml problems
                        Grant Perkins

                        Hi Marie,

                         

                        I think the key to this is to get to the base levels and extract those. After that working at higher aggregated levels, like Company, should be relatively easy.

                         

                        The potential for variables looks very familiar. I ran a project on some data held in separate files but which resulted in pretty much the same problems to overcome.

                         

                        An example of the xml with all the potential variables as examples (real or otherwise) could well be useful but it might be a good idea to see what Kruncher has to suggest first. Could probably save a lot of time.

                         

                        If the investigation needs to continueafter that  just let me know and I will provide an email address to which you can send a sample file.

                         

                        HTH.

                         

                        Grant

                        • payroll register xml problems
                          Mike Urbonas

                          Mariel, your Monarch for XML challenge is definitely in good hands with Grant and Data Kruncher.

                           

                          Throwing my .02 in as well, I think the path of least resistance would be to use a series of Monarch models to mine certain multiple types of data only.  For example, mine only the multiple earnings types with amounts and codes in one model, multiple withholdings in another model, etc.  Take care with each model to also mine some append level data that is common across all models, such as the employee ID or SSN.

                           

                          Once the data results of each of these models is exported (perhaps Excel or Access), you can then use Monarch Pro to combine the data together using the Employee ID or SSN as the 'join' field.

                           

                          And finally I recommend creating s separate model to mine the detail HR related employee info (ie, from Employee ID to Hire Date).

                           

                          One important note though is Monarch V8 Pro limits you to combining 9 different data sources within one session of Monarch.

                           

                          Does this suggested approach help?

                          Mike

                          • payroll register xml problems
                            Grant Perkins

                            Originally posted by Mike Urbonas:

                             

                            One important note though is Monarch V8 Pro limits you to combining 9 different data sources within one session of Monarch.

                             

                            Does this suggested approach help?

                            Mike /b[/quote]Good point from Mike - which, in conjunction with something I 'discovered' the other day, got me thinking.

                             

                            Effectively you need to 'flatten' the structure of the report and one way of doing this is to take chunks out and use them as look up tables.

                             

                            This could also be done using calculated fields within Monarch (using as many iterations as required).

                             

                            So for example eaach 3 line block of "OtherHoursAndEarnings" would be treated as a multiline field and manipulated to remove the trailing part of the tag and whatever else is not required. This would leave a single field with a known structure - Code, Hours, Amount. The field could then be parsed as required to produce fills for the individual fields OR left like that and exported as part of a new report format  (multiple sections could be amended at the same time) which should be more in line with the direct access to the data fields that you require.

                             

                            It does get a little more complicated if you might have several lines of the same 'tag', even if the codes vary, but it should be possible to make a revised report 'flat' enough to  run quite a normal model against it. (Or at least that is what I would hope.)

                             

                            I would expect such an approach to eliminate or at least reduce the needs for lookups and so reduce the potential for running out of links.

                             

                            HTH.

                             

                             

                            Grant