4 Replies Latest reply: May 15, 2014 10:03 AM by Joe Berry RSS

    Handling Hierarchical Data

    kenjfox _

      I have a report (UB hospital claim data) that needs to be exported into 2 tables with a parent-child relationship.  The parent table would contain all of the data relevant to the patient, and the child table would contain all of the detail charge information.  One patient claim can span more than one page.  In these cases the top and bottom of the claim is repeated.

       

      My challenge is to create a unique key in both tables so that the appropriate claim header and detail are joined together in a database.

       

       

      Unfortunately it's possible that 2 virtually-identical claims could exist in a file, without knowing what the differences are (due lots of fields in each claim).  So far, I've not been able to create a means to support this scenario.

       

      I'm able to join the data using the page number, but I end up with duplicate parent records on multi-page claims.

       

      What I think would work if it existed, would be a "First Page Number" value upon occurance of a template.

       

      This is an easy process using VorteXML, since you can set the expected order of templates.

       

      Any ideas on how to do this in Monarch?

       

      Here's a sample report:

      HEALTHRUS SANTA DOMINGO                                  RRFN5732

      POB 280                                              RS05615448             0131

      SAN   CLARA CA 78691                                 0000

      2107042221 2107043758                             741109665 051408 052808

                                               118 CAMEO

      Apatient ALEJANDRA              SAN CLARA                      CA  786140000

       

      12081994 F 051408 16 3  1  09 01

       

      11 051408 29 051608 35 051608

       

       

      Apatient DENISE                              50       300 80      1600  

      118 CAMEO                                                                               

      SAN CLARA CA 78614                                                  

       

      0420 THERAPEUTIC ACTIVITIES  97530GP        051408       5     38000

      0420 THERAPEUTIC ACTIVITIES  97530GP        052108       5     38000

      0420 THERAPEUTIC ACTIVITIES  97530GP        052808       5     38000

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      0001       1   1                            070108            114000       000

                                                                         1881601839

      ULTRA STAR HEALTH 999990015      Y  Y       000        000    HH0104

                                                                                      Apatient CARLOS F           19 MCD847887384        MCDONALDS      053211

                                                                                      MCDONALDS

       

       

      V571    3430    7812

                      

                     V571                      000         

                                                               1386751360    

                                                          CAMPBELL          ROBERT

                                                                                      B3273Y00000X                                  

      BLUE CROSS                                         

      PO BOX 655730                                                          

      DALLAS CA 75265                                                                               

      SSI ELECTRONIC

      HEALTHRUS SANTA DOMINGO                                  RCFN5733

      POB 280                                              RS06470207             0131

      SAN   CLARA CA 78691                                 0000

      2107042221 2107043758                             741109665 062708 062708

                                               PO BOX 736

      ANOTHER DARREN                  RODEO                            CA  788610000

       

      12192005 M 062708 11 3  1     01

       

      11 062708

       

       

      ANOTHER AMANDA                                                          

      PO BOX 736                                                                               

      RODEO CA 78861                                                        

       

      0301 MOLECULAR DIAGNOSTICS   83892          062708       3     15300

      0301 MOLECULE GEL ELECTROPHO 83894          062708       3     13500

      0301 GENETIC EXAMINATION     83912          062708       1     11000

      0301 MOLECULE NUCLEIC TRANSF 83897          062708       2      9000

      0301 MOLECULE NUCLEIC AMPLI  83901          062708       1      8900

      0301 MOLECULE ISOLATE NUCLEI 83891          062708       1      4500

      0301 MOLECULAR DIAGNOSTICS   83896          062708       1      2800

      0309 MOLECULAR DIAGNOSTICS   83896          062708       1      6200

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      0001       1   1                            070108             71200       000

                                                                         1194787218

      SUPER STAR HEALTH   999990534      Y  Y       000        000    1011RC

                                                                                      ANOTHER DARREN              18 526303655

                                                                                      NOT EMPLOYED

       

       

      75983 

                      

                     75983                     000         

                                                               1922001395   0BCABE8526

                                                          GROSS             SHELDON

                                                                                      B3282N00000X                                  

      SUPER STAR HEALTH                               

      4801 NW LOOP 410 SUITE 1                                               

      SAN CLARA CA 78629                                                                               

      SSI ELECTRONIC

      HEALTHRUS SANTA DOMINGO                                  RCFN5733

      POB 280                                              RS06470207             0131

      SAN   CLARA CA 78691                                 0000

      2107042221 2107043758                             741109665 062708 062708

                                               PO BOX 736

      ANOTHER DARREN                  RODEO                            CA  788610000

       

      12192005 F 062708 11 3  1     01

       

      11 062708

       

       

      ANOTHER AMANDA                                                          

      PO BOX 736                                                                               

      RODEO CA 78861                                                        

       

      0301 MOLECULAR DIAGNOSTICS   83892          062708       3     15300

      0301 MOLECULE GEL ELECTROPHO 83894          062708       3     13500

      0301 GENETIC EXAMINATION     83912          062708       1     11000

      0301 MOLECULE NUCLEIC TRANSF 83897          062708       2      9000

      0301 MOLECULE NUCLEIC AMPLI  83901          062708       1      8900

      0301 MOLECULE ISOLATE NUCLEI 83891          062708       1      4500

      0301 MOLECULAR DIAGNOSTICS   83896          062708       1      2800

      0309 MOLECULAR DIAGNOSTICS   83896          062708       1      6200

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      0001       1   1                            070108             71200       000

                                                                         1194787218

      SUPER STAR HEALTH   999990534      Y  Y       000        000    1011RC

                                                                                      ANOTHER DARREN              18 526303655

                                                                                      NOT EMPLOYED

       

       

      75983 

                      

                     75983                     000         

                                                               1922001395   0BCABE8526

                                                          GROSS             SHELDON

                                                                                      B3282N00000X                                  

      SUPER STAR HEALTH                               

      4801 NW LOOP 410 SUITE 1                                               

      SAN CLARA CA 78629                                                                               

      SSI ELECTRONIC

       

      /CODE

        • Handling Hierarchical Data
          Grant Perkins

          Welcome to the forum kenjfox!

           

          VorteXML eh? Interesting.

           

          Does the report really look as it has pposte or has something messed it up along the way?

           

          I think I know what you suggesting but I'm having difficulting visualising things with that report format.

           

           

          I would imagine that is all the data in both tables you refer to comes from a single source - indeed a single client record or something close to that set if I understood correctly - then creating a common key that os exported to two table ought to be possible. Scheme membership number or something? Some combination of fields (though care may be required to retain flexibility should any changes come about.)

           

          My first reaction would be to see if the top and bottom areas of the claim could be set up as Appends/Footers and dealt with like that. However that may depend on how you are 'seeing' the report's structure and thus how you are defining the templates.

           

          If the problem is that for the 'Master' record the detail is always what will be duplicated IF and WHEN a a record crosses a page break the idea occurs to me that one might be able to select more or less as normal and then deduplicate the resulting table, perhaps using a summary along the way.

           

          However, as I said, I don't feel I am 'reading' the report well at the moment so will reserve judgement a while longer.

           

          HTH.

           

           

          Grant

            • Handling Hierarchical Data
              kenjfox _

              "Does the report really look as it has pposte or has something messed it up along the way? " 

               

              Not sure what you mean by 'pposte'.  The sample file appears pretty well intact.  It contains 2 virtually identical claims, except one has an "F" for patient sex and the other has "M".  In effect, the report has 2 "headers" per page, with detail in the middle.

               

              I have a project/model if you'd like to see it- let me know where to send/upload.  I did take the summary approach, and defined keys that in most cases should make a claim record unique.  However, I really want to use the position of the claim within the file to uniquely identify it, so I don't have to worry about slight variations in the claim "header" data.

                • Handling Hierarchical Data
                  Grant Perkins

                  "pposte"

                   

                  Hmm. Browser running slow  - it's a typo, though I could claim a stutter?

                   

                  "posted" would the the right substitution.

                   

                  What I see is a few relatively formatted lines absent any descriptive field headers followed by what looks like a random array of some data fields and a lot of white space, then another section that sort of looks like a  page header without an obvious page number and finally a few more relatively structured lines. And then it repeats, I think.

                   

                  Is that how it is or have I got something odd going on with fonts and displays?

                   

                  Or is it the result of a screen scraper of some sort?

                   

                  "so I don't have to worry about slight variations in the claim "header" data. "

                   

                  If the two table extracts are always coming from the same report, how would that happen without being correctable? How would position in the report be any more accurate?

                   

                  Have you considered establishing your own pagination in Monarch by defining the page header to be somewhere useful for you purposes? It may not be possible unless you have a consistent way to ID that start of each claim ... and if you have that my instinct says you can probably extract anyway based on the data format/content pattern alone.

                   

                  HTH.

                   

                   

                  Grant

                    • Handling Hierarchical Data
                      Joe Berry

                      I am familiar with UB-04 data and the layout.  Without the form behind the data it does not resemble a report; however, there is a multitude of goodies hiding.

                       

                      I would use the patient control (account) number (UB Field Locator 03a) as the unique key for the demographics and the charge data.  No two patients will ever have the same patient account number.  Map the fields that you need for the demographics data in a project/model and summarize the data needed using the patient control (account) number (UB Field Locator 03a) as the key field.

                       

                      This will eliminate duplicate unique keys and allow you to export this to access.

                       

                      A second model and project will allow you to define the charge data, again using the patient control (account) number (UB Field Locator 03a) as the unique key.  Once the data is defined it can then be exported.

                       

                      I hope this helps.  If you have questions, just let me know.

                       

                      Joe Berry