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

Alteryx designer Discussions

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

Multiple lines to 1: Prioritize payment methods

One of my input files has numerous options of payment methods for each vendor like so:

 

VendorBank
V000025ACH
V000025CHK
V000025CCR
V000025SPSA
V000026CHK
V000026CCR
V000027CHK
V000027ACH

 

I am looking for a work flow that will return only 1 row per vendor with the desired payment method. ACH is always our first choice, followed by CCR, CHK, and SPSA (in that order). For instance, using the example above I would want the output to be:

 

VendorBank
V000025ACH
V000026CCR
V000027ACH

 

Thanks!

Bolide

Enter the data from your second table into a Text Input tool, and include a column for "Choice Number"

 

Join from your first table to your second table on Vendor and Bank and include the field for Choice Number

 

Sort on Vendor, Bank, and Choice Number

 

Use the Summarize Tool, group by Vendor and Bank, and for the field Choice Number use Action = First

 

Highlighted

Thanks, Chris. 

 

I'm afraid I may have simplified the tables above quite a bit. The payment method options are:

BankTranslated 
A1AAHACH
A1AATACH
A1AFDACH
A1AHNACH
A1AICACH
A1AMHACH
A1AMRACH
A1CCSACH
A1CFCACH
A1CSJACH
A1ECSACH
A1GCTACH
A1GJHACH
A1NSMACH
A1RASACH
A1RNAACH
A1RNA 2ACH
A1ROHACH
A1RPAACH
A1SLAACH
A1SSHACH
A1STXACH
A1SWPACH
A1TELACH
A1TJPACH
A1TMRACH
ACH AATACH
ACH AMHACH
ACH AMRACH
ACH CCSACH
ACH CFCACH
ACH CSJACH
ACH ECSACH
ACH NSMACH
ACH RASACH
ACH RNAACH
ACH ROHACH
ACH RPAACH
ACH SLAACH
ACH STXACH
ACH SWPACH
ACH TELACH
ACH TJPACH
ACH TMRACH
ACH1 RNAACH
ACH1 SSHACH
PMX AAHACH
PMX AATACH
PMX AMHACH
PMX AMRACH
PMX CCSACH
PMX CFCACH
PMX CSJACH
PMX ECSACH
PMX NSMACH
PMX RASACH
PMX RNAACH
PMX RNA 2ACH
PMX ROHACH
PMX RPAACH
PMX SSHACH
PMX STXACH
PMX SWPACH
PMX TELACH
PMX TJPACH
PMX TMRACH
CCR AAHCCR
CCR AATCCR
CCR AMHCCR
CCR AMRCCR
CCR CCSCCR
CCR CFCCCR
CCR CSJCCR
CCR NSMCCR
CCR RASCCR
CCR RNACCR
CCR ROHCCR
CCR RPACCR
CCR SSHCCR
CCR STXCCR
CCR SWPCCR
CCR TELCCR
CCR TJPCCR
CCR TMRCCR
CCR1 RNACCR
IC AATIC
IC AMHIC
IC AMRIC
IC CCSIC
IC CFCIC
IC ECSIC
IC NSMIC
IC RASIC
IC RNAIC
IC RNA 2IC
IC ROHIC
IC RPAIC
IC SLAIC
IC SSHIC
IC STXIC
IC SWPIC
IC TELIC
IC TJPIC
IC TMRIC
SPSA AMHSPSA
SPSA CCSSPSA
SPSA CFCSPSA
SPSA NSMSPSA
SPSA RASSPSA
SPSA RNASPSA
SPSA ROHSPSA
SPSA RPASPSA
SPSA SSHSPSA
SPSA SWPSPSA
SPSA TELSPSA
SPSA TJPSPSA
SPSA TMRSPSA
SPSA1 RNASPSA
SPSC AATSPSC
SPSC AMHSPSC
SPSC AMRSPSC
SPSC CCSSPSC
SPSC CFCSPSC
SPSC CSJSPSC
SPSC NSMSPSC
SPSC RASSPSC
SPSC RNASPSC
SPSC ROHSPSC
SPSC RPASPSC
SPSC SSHSPSC
SPSC STXSPSC
SPSC SWPSPSC
SPSC TELSPSC
SPSC TJPSPSC
SPSC TMRSPSC
SPSC1 RNASPSC

 

As you can see I will have some vendors with multiple different options and I was looking for a work flow that would say if any of the Translated payment methods = ACH, then use ACH. 

Labels