Healthcare Patient Product Order Transaction History: pick the primary Insurance Plan

[garycao]

Hi,

I have a transaction history with following attributes:

• Date of Shipment
• Customer ID
• Order ID
• Product Item ID
• Sales Dollar
• Insurance Plan ID
• Referral Source ID

Over 6 months period, a customer can have multiple orders; each order can have multiple product items.  Most of the time, “Insurance Plan ID” and “Referral Source ID” are the same, but may also be different.

I want to choose “Insurance Plan ID” based on the largest value of “Sales Dollar” during this period.  Alternatively, I may want to choose “Insurance Plan ID” based on the date (first-ever “Date of shipment” during this period of 6 months).

Additionally and separately, I want to choose “Referral Source ID”, based on largest “Sales Dollar” at Order level, similar to “Insurance Plan ID” situation above.

How do I do that?  Thank you for your help.

[jdunkerley79 · Accepted solution]

Yes perfectly doable.

For max dollar sales, use a summarise tool to create the total by customer and plan. You can then use a sort tool and sample tool to get one for each customer.

For the first plan, again a summarise tool will get the min date. You can then join back to the original data and again use a sample tool to get the insurance plan id.

Sample attached. You should be able to adjust it to for referral source id in a similar vain.

[garycao]

Thank you!  Very helpful, and quick.