Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Delete Non Contracted Data.

amitsingh88
8 - Asteroid

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

 

 

 

6 REPLIES 6
MattBSlalom
11 - Bolide

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
8 - Asteroid

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
8 - Asteroid

Thank. I got the desired result.

 

amitsingh88_0-1617413572582.png

 

amitsingh88
8 - Asteroid

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
11 - Bolide

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
8 - Asteroid

Thanks a lot.

Labels