Joining two files with mismatching rows

[swpaeng00]

Hello Community,

I'm new to Alteryx and having trouble with joining two data sets with mismatching rows.

As the title says, I have two data sets that need to be joined together, but have uneven number of rows, and 

some rows even have different identifiers.

I'm hesitant to use Generate Rows because I need to sum the values in columns X and Y (across, not down)

Is there a way to join the two data sets and join the rows that match, while outputting 0's or nulls for rows that do not?

Thank you!

[Felipe_Ribeir0 · Accepted solution]

Hi @swpaeng00 

You can union the L, J, R anchors of the join tool, so you will have even the unmatched rows (L and R anchors) on the same dataset of the matched rows (J anchor)

[danilang]

Hi @swpaeng00 

Can you provide small samples of the data that you're working with as well as the rules used to match when the IDs don't coincide?  Without samples, the possible solution space is huge. 

Dan

[swpaeng00]

Hi @danilang @Felipe_Ribeir0 

Attached is a small sample of something I'm working with.

I have two files with columns for: Company Code, Plant, Product Code, Code Combination, and the value that needs to be summed.

I need the two files to be line up by all three codes (Company, Plant, and Product [and by extension the code combi]) in that respective order,

and then the values in those two files to be lined up next to each other. 

if one of the rows in one of the files do not have a match, I want it to have a row of its own.

I also put the desired form of it in the example.

[danilang]

Hi @swpaeng00 

You just need to join and union.  

@Felipe_Ribeir0 had the right answer here, so make sure to mark his response as the solution

BTW.  Your second input had an error.  The first combicode was 1002A1015001 and should have been 1001A1015001. 

Dan