Alteryx Designer Desktop Discussions

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

Joining data sets with multiple criteria

sselby6909
6 - Meteoroid

Apologies if this has already been answered in another post, but I wasn't sure how to word this in my search...

 

I'm trying to combine two data sets so that I can create a formula to override another column based on the joined files. I don't want to create a lot of If statements to override another column based on several variables.  

 

I have two data sets from excel files I'm trying to join:

 

Data Set 1 
CompanyBranchAcct #Acct#2Amount
Company Axyz12345675.99
Company Babc56789026.99
Company Cdef90121527.99

 

Data Set 2
CompanyAcct #Acct2
Company A1234123
Company A5678456
Company A9012789
Company B9012682
Company B1234789
Company B5678456
Company C1234234
Company C5678789
Company C9012123

 

Output desired
CompanyBranchAcct #Acct#2AmountAcct2
Company Axyz12345675.99123
Company Babc56789026.99456
Company Cdef90121527.99123

 

The formula would then overwrite Acct#2 with Acct2 for an output of 

Output desired after formula
CompanyBranchAcct #Acct#2Amount
Company Axyz12341235.99
Company Babc56784566.99
Company Cdef90121237.99

 

Further down stream these will be sumed on the amount field based on Company, Branch, and Acct #2

 

Final output downstream
CompanyBranchAcct#2Amount
Company Axyz1235.99
Company Babc4566.99
Company Cdef1237.99
7 REPLIES 7
Clifford_Coon
11 - Bolide

Hi @sselby6909 ,

 

You can do it all using the "select" features of the join tool.

Join.jpg

 

Happy Solving ;-)

binuacs
20 - Arcturus

@sselby6909 another option

binuacs_0-1686322920526.png

 

sselby6909
6 - Meteoroid

 that this tends to override Acct #1 from the left input. I want that to remain untouched. 

binuacs
20 - Arcturus

@sselby6909 @If you want to include the Acct# then include that field also in the group by of summarize tool

 

binuacs_0-1686324791025.png

 

nagakavyasri
12 - Quasar
sselby6909
6 - Meteoroid

Thanks everyone. I think I figured out the issue. I had duplicates and it wasn't sure which one to use. I've cleaned up the duplicate data. However, now i'm having an issue with writing an if statement. 

 

I'm trying to get something like this

 

If Opco = A and Function  = XYZ and Code = Null, and descr = A or B or C then 1234

 

I wrote as follows: IF[Ocpo] = "A" AND [FUNCTION]="XYZ" AND [CODE] =null() AND [Descr] = "A" OR "B" OR"C" Then 1234, but it's not recognizing the OR in the description = syntax. 

 

nagakavyasri
12 - Quasar

@sselby6909 use  [Desc]='A' or [Desc]='B' or [Desc]='C' (OR)  [Desc] in ('A','B','C') to use OR function

Labels