1 Reply Latest reply on Oct 11, 2018 4:25 AM by Yuriy Fal

    Provider Attribution Calculation

    Jim Jeffcoat

      I've been been struggling with solving my lookup issue for days.... OK, so I just installed Tableau last weekend.

       

      Now its time to ask the experts. Hopefully I can explain it well enough to get some help.

       

        I have two tables...

        

      1. Patients

       

      Field: Patient_ID

       

      Field: PCP_ATTRB (This will be the calculated field)

       

       

      Values in the Table

       

      Patient_ID

      PCP_ATTRB

      123

       

      456

       

      789

       

       

       

      2. Claims

      Field: Patient_ID

      Field: Provider_ID

      Field: Service_Date

       

      Values in the Table

       

      Patient_ID

       

      Patient_ID

      Provider_ID

      Service_Date

      123

      888

      1/4/2018

      123

      999

      1/5/2018

      123

      888

      1/4/2018

      456

      777

      1/11/18

      456

      555

      1/1/18

      789

      555

      1/12/18

      123

      999

      1/6/2018

      123

      999

      1/6/2018

      123

      888

      1/10/2018

      123

      888

      1/11/2018

      123

      888

      1/15/2018

       

      For each Patient_ID in table 1, I need the code to lookup all the rows in table 2 that have the same Patient_ID value, then find the Provider_ID with the most Service_Dates, then return that Provider_ID back to table 1 in the PCP_ATTRB field.

       

      So the code would look for Patient_ID "123" in table and find 8 matching rows. For Provider_ID "999" it would find 3 rows but only 2 unique Service_Date values. For Provider_ID "888" it would find 5 rows, but only 4 unique Service_Date values. Since Provider_ID "888" has more unique Service_Date values, return the Provider_ID "888" back to table 1 calculated PCP_ATTRB.

       

      In the event that two Provider_ID values in table 2 have the same number of unique Service_Date values, return the Provider_ID with the most recent Service_Date. Patient_ID 456 would bring back Provider_ID "777" since it has the most recent Service Date of 1/11/18.

       

      Table 1 should look like this after the lookup. Thank you in advance for any help or suggestions in resolving my problem. Jim Jeffcoat

       

       

      Patient_ID

      PCP_ATTRB

      123

      888

      456

      777

      789

      555