4 Replies Latest reply: May 15, 2014 9:53 AM by Data Kruncher RSS

    Recommended approach for a specific data format

    kmpirish _

      Hello all,

      I'm working on a report extraction that has me stumped.  I think the solution is easy but I've been looking this data so long, I've burned out and I can't see it.

       

      I work for a hospital and I'm extracting data from a report that lists patients and their health insurance information.  Here is a sample:

       

      [font="courier"]123456     PTADD  WEASLEY    RONALD

      1 THE BURROW                           

      OTTERY ST. CATCHPOLE, ENGLAND 12345        

      A51  UNITED WIZARDING HEALTH       PRIMARY           

      C37  GALLEONSINK HEALTH ALLIANCE   SECONDARY    

                                                                          

      123456     SSA51  WEASLEY   ARTHUR                

      1 THE BURROW                           

      OTTERY ST. CATCHPOLE, ENGLAND 12345   

      A51  UNITED WIZARDING HEALTH       PRIMARY           

      C37  GALLEONSINK HEALTH ALLIANCE   SECONDARY    

                                                                          

      123456     SSC37  WEASLEY   MOLLY                 

      1 THE BURROW                           

      OTTERY ST. CATCHPOLE, ENGLAND 12345    

      A51  UNITED WIZARDING HEALTH       PRIMARY           

      C37  GALLEONSINK HEALTH ALLIANCE   SECONDARY[/font][/quote]The first five rows of data refer to the patient.  The second five rows refer to the policy holder of the United Wizarding Health plan - as identified by the plan code A51 to the left of the policy holder's name and it's listing beneath the policy holders address.  The third 5 rows refer to the policy holder of  the Galleonsink Health Alliance plan - as identified by the plan code C37.

       

      My final data extraction needs to list the patient, their insurance plans and policy holders all in one row, something like this:

       

      [font="courier"]123456,WEASLEY,RONALD,PRIMARY,UNITED WIZARDING HEALTH,WEASLEY,ARTHUR,SECONDARY,GALLEONSINK HEALTH ALLIANCE,WEASLEY,MOLLY[/font][/quote]I've tried to make this happen by changing which of my templates is the detail template and with summaries but I can't seem to get a good view of the big picture and the approach I need to take.

       

      Thank you in advance for advice - let me know if I need to clarify or be more specific.

       

      Kate

        • Recommended approach for a specific data format
          Joe Berry

          Kate,

          Have you tried using a detail template with all 15 lines of data?  I think that would work.

          Joe Berry

          • Recommended approach for a specific data format
            Data Kruncher

            Kate,

             

            Please check your Private Messages.

             

            Thanks,

            Data Kruncher

            • Recommended approach for a specific data format
              kmpirish _

              Just to follow up - Data Kruncher sent along a solution that worked.

               

              The solution goes like this:

              1) Create one model that extracts the data and creates one record per person.  Using a calculated field and some well chosen If statements, you can create a field that identifies the data.  Thus, the output would look like this:

               

              [font="courier"]123456,Weasley, Ron, <Ron's Info>, Patient

              123456,Weasley, Arthur, <Arthur's Info>, Primary Insurance Holder, <Primary insurance company info>

              123456,Weasley, Molly, <Molly's Info>, Secondary Insurance Holder, <Secondary insurance company info>  /font[/quote]2) You can now export this list to an Excel file for use in an external lookup.

               

              3) Once the export is complete, filter your current list to show Patient information only (now is a good time to Save As and create your second model).  Then create the first External Lookup - matching records based on patient number (123456 in my example) and I filtered first for Primary Insurance Holder.  Rinse and repeat for Secondary Insurance Holder and you get the format I needed:

               

              [font="courier"]123456,Weasley, Ron, <Ron's Info>, Weasley, Arthur, <Arthur's Info>, <Primary insurance company info>, Weasley, Molly, <Molly's Info>,  <Secondary insurance company info>  /font[/quote]Thank you, Data Kruncher, for taking the time to send the examples to me - it's very much appreciated!!

               

              I've run into one problem with the External Lookup - it truncates some of my fields.  Using some advice from [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000899#000001"]this thread[/url] I think I need to adjust the formatting of some cells in the Excel data source file (e.g. the CITY field gets truncated when it's the cell format is "General" but not when it's "Text")

               

              Thanks,

              Kate

              • Recommended approach for a specific data format
                Data Kruncher

                Glad to hear that this worked out for you Kate.

                 

                Thanks for letting us know about your progress.

                 

                Kruncher