community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.9

The latest release includes several enhancements designed to improve your Community experience!

Learn More

Dynamically Join based on Excel Reference file

Asteroid

I am creating an analytic app to help one of our account teams create customer invoices. One of the first steps is to make sure each bill has the correct reference numbers. To do this the customer has provided us a reference document with many business rules that boil down to if column C = X then change column A to Y. I am the only person in my department with an Alteryx license so any changes to analytic apps must be done by me. This customer revises their reference sheet every month so I am trying to come up with a way to use this reference document to control filters and formula tools and avoid hard coding as many rules as possible so I do not have to make these changes each month.

 

My goal is to come up with a process that uses the attached Sample Reference File to dynamically update Join, Filter, and/or Formula tools to update the Account Number column in the Sample Invoice file. Then whenever our customer updates their reference files my account team can attach the new reference file and I will not have to make any changes.

 

Assumptions

- Order Column is important. Rule 4 is the most important and should overwrite any changes made by rule 1, 2, or 3.

- If any columns of the Sample Invoice File match any of the non blank "Current" columns on the the reference sheet you should update the Account Number  on the Sample Invoice File.

 

I hope I've provided enough information. Let me know if you have any questions or any ideas about how to do this.

Alteryx Certified Partner
Alteryx Certified Partner

Is this what you are looking for? Im not sure

Asteroid

afv2688 - Sort of, but that would only work for line 1 of my reference file. 

 

Line 1 - update account number if Current Account Number = 125673

Line 2 - update account number if Current Origin NCS = EMOPLATX and Destination NCS = UPWEHACT

Line 3 - update account number if Current PO = KIT 01321714 DEP 1296623

 

This is only a sample reference filwe. The actual file is about 75 lines long and can have any combination of the "Current" columns. I am trying to see if there is a way to do this without adding Join, Find Replace, Filter, Formula tools for each row.

Labels