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:
Fund | Cusip | Shares |
A | ABC | 1000 |
A | DEF | 2000 |
B | DEF | 3000 |
C | GHI | 4000 |
D | JKL | 2 |
Source2:
Fund | Cusip1 | Cusip2 | Cusip3 | Cusip4 | Shares |
A | ABC | 1000 | |||
A | DEF | DEF | 2001 | ||
B | DEF | 3000 | |||
C | GHI | GHI | GHI | GHI | 4000 |
E | MNO | 15 |
Ideal End Result:
FundCusip | Shares_Left | Shares_Right |
A-ABC | 1000 | 1000 |
A-DEF | 2000 | 2001 |
B-DEF | 3000 | 3000 |
C-GHI | 4000 | 4000 |
D-JKL | 2 | NULL |
E-MNO | NULL | 15 |
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
Solved! Go to Solution.
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
Sample attached