7 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Floating data question

    kmpirish _

      Hello,

       

      I'm new to using Monarch and I've run into a situation with my report that has me stumped.  Below is an excerpt (edited for privacy) which contains two records:

       

      [font="courier"]07/01/195002/14/20071546  57  02/15/2007      1234561A00736701 13NNN0376FEOSSOLO                                                  

      HAN                                                      3310 MAIN STREET              APARTMENT 1013                        

      ANYWHERE                 MD22222     112233      9HR                                                                            

      05/20/194002/08/2007203   67            654321      PP00110197 2        MEOEJONES                                                  

      INDIANA                                                      1234 FIRST STREET              (MAILING ONLY)                        

      ANYWHERE                  MD22222     3322110     8HR                                                                               

      /font[/quote]To orient you to the data - the first three lines are Han Solo's record and the second three are Indiana Jones'.

       

      I need to extract Han's and Indy's medical record number (MRN) from this data.  The MRN is in the first line of each record; in the example above, Han's is 123456 and Indy's is 654321.

       

      I tried a floating trap, but I couldn't get it to work - it picked up other numbers as well.  I had a difficult time with this, especially because not all of this data is delimited (for example the "13NNN0376FEOSSOLO" in Han's record represents 7 different pieces of information).

       

      The closest I've come to meeting my needs is by using two templates.  I successfully get my data trapped that way, but that leads me to problem two: I need these data to be one field in the Table View.  For example:

      [font="courier"]LastName      MRN

      Solo          123456

      Jones         654321  /font[/quote]Thank you in advance for any help/advice.

       

      Kate

        • Floating data question
          Grant Perkins

          Hi Kate and welcome to the forum.

           

          I just wanted to confirm that the somewhat variable positioning of some data is accurately represented in the post (which I think it is as it seems to be part of your problem as described). It is not unknown for 'cleansed' reports prepared for posting to have a few anomalies and then be further amended by the re-formatting during posting!    

           

          It is slightly unusual for that type of output not to have a fixed structure although variable fonts in some applications can produce that apparent effect. Are you working from a fixed font original file or something that has been through a few conversions?

           

          I'll continue to try to come up with some ideas anyway.

           

           

          Grant

          • Floating data question
            kmpirish _

            Thank you!

             

            Originally posted by Grant Perkins:

            It is not unknown for 'cleansed' reports prepared for posting to have a few anomalies and then be further amended by the re-formatting during posting!      

            /b[/quote]Yes, indeed a few such anomalies made it into my post.  In the original report, all fields are in the same position except the MRN.  So, if you're working with the sample I posted, it's okay to line up "Apartment 1013" and "(Mailing Only)" City, StateZIP, and the "8HR" and "9HR" since that's how it is in the report I'm using.

             

            The post is displaying the first line of each record correctly.

             

            Unfortunately, I'm working with an original file - fixed font and no conversions.

             

            Thanks,

            Kate

            • Floating data question
              Grant Perkins

              Originally posted by kmpirish:

               

              The post is displaying the first line of each record correctly.

               

              Unfortunately, I'm working with an original file - fixed font and no conversions.

               

              Thanks,

              Kate /b[/quote]Ah Ha! Thats good to know. Narrows the problems and possible identifiers down somewhat.

               

              Right, that leads to the next question.

               

              Does the position of the vary only when there is or is not a date preceding it?

               

              (I'm not sure whether the line is 'correctly' formatted with or without a date. There may also be other inserts that cause anomalies. The date may be nothing to do with it at all ...  Still needs to be eliminated or fingered though!)

               

               

              Grant

              • Floating data question
                kmpirish _

                Does the position of the vary only when there is or is not a date preceding it?  /quoteThe position of the MRN is dictated by whether the patient was in our Emergency Room or was admitted and stayed as an inpatient.  In the example I gave, Han is an inpatient, which is why he has so much more information on the first line.  Indiana was in the emergency room (more asps, I guess       )

                 

                In my report, emergency room patients are indicated by a letter E.  In Indy's record, it is the E just before the J in Jones: MEO[b]E[/b]JONES 

                 

                Having fun yet?    tongue.gif[/img] 

                 

                Thanks,

                Kate

                • Floating data question
                  Grant Perkins

                  Originally posted by kmpirish:

                  Having fun yet?            tongue.gif[/img]         

                  /b[/quote]Not had so much fun since the last time I had a tooth drilled without effective pain killer!

                   

                  Here we go ....

                   

                  Sounds like you need to be into conditional processing. Ther emay be more than one rule at play in the report but let's use the example you mentioned.

                   

                  You can pick the rule according to the character before the start of the patient's name. If you identify the variables associated with that code (in this case it seems to be the string of potential data from the date to somewhere before the code(s) before the patient's name on the first line you can select that entire section as a single field. The 'rules' assigned by the "E" code (in this case) can then be applied to that string in order to slice and dice it. Do this using a calculated field for each field you need that is contained in the 'variable data' string.

                   

                  If we take the variables as the data below;

                   

                  [font="courier"]02/15/2007      1234561A00736701

                                          

                  ..........654321      PP00110197                                           /font[/quote]... we can substring (Function SUBSTR) that 'field' to extract the block of characters we need. You will know the length on f the field (or use the LEN function) and presumably the position of the MRN will always be in a known location relative to the start (or end) of the field for a known "E" etc. flag.

                   

                  So if there are two possibilities combine the SUBSTR finction in an IF() function and you would get something like

                  [font="courier"]IF(Efield="E", SUBSTR(STRINGFIELD,11,6),SUBSTR(STRINGFIELD,17,6))[/font][/quote]If there are more than 2 options - for instance no code at all - you can 'nest' the IF formulas ...

                   

                   

                  [font="courier"]IF(Efield="E", SUBSTR(STRINGFIELD,11,6),if(Efield="S", SUBSTR(STRINGFIELD,17,6)), "ERROR")[/font][/quote](I may have missed some parentheses in there!)

                   

                  It can also be written as ...

                   

                  [font="courier"]IF(Efield="E", SUBSTR(STRINGFIELD,11,6), "")+

                  IF(Efield="S", SUBSTR(STRINGFIELD,17,6)), "")+

                  IF(Efield="","BLANK", "ERROR")[/font][/quote]Or something along those lines.

                   

                  Those formulae relate to an Character field, which I assume the MRN probably needs to be. If not you will a variation on the theme or simply convert the results using the VAL function.

                   

                   

                  [font="courier"]IF(Efield="E", VAL(SUBSTR(STRINGFIELD,11,6)), 0)+

                  IF(Efield="S", VAL(SUBSTR(STRINGFIELD,17,6))), 0)+

                  IF(Efield="","000000", "999999")[/font][/quote]Or similar.

                   

                  Does this help?

                   

                  If not let us know where the problems start!

                   

                   

                  Grant

                   

                  PS - I did this in a hurry so cannot guarantee the accuracy of the formulae above - the Help files can provide more input if you need it!

                   

                  Have fun.

                  • Floating data question
                    kmpirish _

                    A ha!!!!  Thank you for the inspiration!  I ended up making the template width of the MRN field 12 - that way, it captures both:

                     

                    [font="courier"]      123456

                    654321       /font[/quote]Then I changed the field type to Numeric - voila!  Nice, pretty numbers. 

                     

                    Thank you for the code examples - I think I will keep them in my back pocket in case my current method becomes inconsistent (They also helped me fix other issues I ran into.)

                     

                    Thank you again!

                    Kate

                    • Floating data question
                      Grant Perkins

                      Originally posted by kmpirish:

                      A ha!!!!  Thank you for the inspiration!  I ended up making the template width of the MRN field 12 - that way, it captures both:

                       

                      [font="courier"]      123456

                      654321       /font[/quote]Then I changed the field type to Numeric - voila!  Nice, pretty numbers. 

                       

                      /b[/quote]Hi Kate,

                       

                      Good thinking!

                       

                      I sort of assumed there may be other data that could drop into the blank spaces in samples under other conditions so went the fully conditional route with my early assessment. If you know that cannot happen then your solution is perfect for its simplicity and less is always more in this respect!

                       

                      Actually it would not be too difficult to spot any anomalies anyway, should they ever arise. If it ever breaks you have a means to fix it!

                       

                      Good work.

                       

                      Grant