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

How to Join/Concatenate Fields from Two Sources (One with Multiple Identifier Columns)

sblank56
5 - Atom

Hello,

 

I'm new to Alteryx so I apologize if this is a simple question, I'm just not sure exactly what the best way to proceed with this is, but basically I'm just wondering if it was possible to take two sources and concatenate/join the 'Fund' and 'Cusip' columns and create a simple summary of just the FundCusip and Share amounts as per the example below- the caveat being the second source actually contains four different Cusip columns, of which only some or all of the fields might be populated 

 

I'm probably not describing this well, but ideally this is how I'd like the end result to look like:

 

Source1:

FundCusipShares
AABC1000
ADEF2000
BDEF3000
CGHI4000
DJKL2

 

Source2:

FundCusip1Cusip2Cusip3Cusip4Shares
AABC   1000
A DEFDEF 2001
B   DEF3000
CGHIGHIGHIGHI4000
E MNO  15

 

Ideal End Result:

FundCusipShares_LeftShares_Right
A-ABC10001000
A-DEF20002001
B-DEF30003000
C-GHI40004000
D-JKL2NULL
E-MNONULL15

 

Would anyone have a suggestion of what the best route to go about solving this would be?  I was thinking maybe just applying a Join tool to the two Input files, but haven't much luck yet.

 

Any help would be greatly appreciated! 

 

Thank you for your time,
Steve

1 REPLY 1
jdunkerley79
ACE Emeritus
ACE Emeritus

Making the assumption that all the Cusip1,2,3 or 4 values are either the same or blank.

 

I would use a transpose tool and then a summarize tool to convert source 2 to look more like source 1.

After this, I would use a join tool followed by a union tool to merge the two tables

 

2017-06-08_13-36-22.jpg

 

Sample attached

Labels