Delete Non Contracted Data.

[amitsingh88]

Hi,

In this File I want to delete those rows where the Insurance is not contracted with Facility in one Go.

This is contract between payers and facilities-

Aetna-BUMC
Medicare-BUMC, Brenham
Medicaid-All Saints
Cigna-Centannial

Like in the row 4, All Saints is showing insurance Cigna, this row needs to deleted as Cigna is only contracted with Centannial not All Saints as shown above.

Can we do this using Macros? Can we make dropdown for payers to select?

Thanks,

Amit

[MattBSlalom]

To get your desired result, you'd want to read in your source file and use a Join tool on both Facility & Insurance against a 2nd dataset that's just the few valid relationships you've listed.  This will filter the source data to the valid rows, then just use an Output Data tool to write out to the same or a new Excel file.  No macro required for this activity.

When you mention a dropdown, I'm not sure if you're meaning within the Excel file or if your referring to creating an Alteryx App with a dropdown.  Neither of these seem necessary for the file cleanup described above.

[amitsingh88]

Hi,

Thanks for your answer. It will  be great. Can you share the workflow in yxzp file? That will be much helpful. I've attached second file too for you convinience.

Thanks.😊

[amitsingh88]

Thank. I got the desired result.

[amitsingh88]

Hi,

There is one more condition. Molina is contracted with Baylor University Medical Centre since 10/01/2020(Case Discharge Date). So need to delete those before this date for Molina-BUMC.

Aetna -Baylor University Medical Centre(BUMC)
Medicare- Brenham
Medicare -Baylor University Medical Centre(BUMC)
Medicaid -All Saints
Cigna -Centennial
Molina- Baylor University Medical Centre(BUMC) since 10/01/2020

[MattBSlalom · Accepted solution]

On your 6 row Facility & Insurance valid relationship data set, you can add a 3rd column for the Effective Start Date.  The value for most of your rows can be a default date like January 1, 1900.  (You could also have those date fields as null or empty if you prefer.)

After you've performed the join on Facility & Insurance, then add a Filter tool setting the condition that the Case Discharge Date is greater than the Effective Start Date (also add an "or" condition for Effective Start Date equal to null or empty if that was your default value of choice).

[amitsingh88]

Thanks a lot.