Matching Fields

[arosenberg1995]

Hi All

I have a file with a number of different investments across two datasets which I have joined into one stream with a number of other fields as well similar to the below example. My goal is to combine the data into one line that matches each investment and all the other fields involved.

Investment	InvestmentFile	Price1	Price2	Field1	Field2	Field3	Field4	Field5	Field6
A				Data	Data	Data	Data	Data	Data
B				Data	Data	Data	Data	Data	Data
C				Data	Data	Data	Data	Data	Data
	A	1	2
	B	3	4
	C	5	6

My desired end result would be similar to the below

Investment	InvestmentFile	Price1	Price2	Field1	Field2	Field3	Field4	Field5	Field6
A	A	1	2	Data	Data	Data	Data	Data	Data
B	B	3	4	Data	Data	Data	Data	Data	Data
C	C	5	6	Data	Data	Data	Data	Data	Data

I have tried using the transpose and cross tab functions but with all the other columns it isn't working well.

Anyone have an idea of how I would accomplish the desired results?

Thank you in advance!

[ScottLewis]

Filter your data stream into two sets on whether the Investment Column is (Blank or NULL) After that you should just be able to do a normal join. It gets a little more complicated if either of the two stacked data sets can have more than one row per Investment but the example doesn't show that. If your data is like that post up and we can talk about how to solve the harder case.

[usmanbashir · Accepted solution]

Instead of appending the two data sets (union), perform a join where the key is on Investment. 

[ScottLewis · Accepted solution]

I made the assumption that the union happened before bringing the data into Alteryx. If the union is something you're doing then usmanbashir's answer is correct, you should just be joining rather than unioning.

If it is coming in stacked like that here's the logic to split it and recombine.

[nagakavyasri · Accepted solution]

@arosenberg1995 Use Join instead of Union. Union appends data vertically, Join appends data horizontally.

[arosenberg1995]

Thank you all, this worked for me. Appreciate it.