How to join and stack at the same time

[kfish]

Hello,

I am trying to two data tables with the exact same structure. For example, My first table might look like,

Code	Value 1	Value 2
1	1.1	Desc
2
3	2.5	Desc

And the second table:

Code	Value 1	Value 2
1
2	2.2	Desc
3

I want it to look like:

Code	Value 1	Value 2
1	1.1	Desc
2	2.2	Desc
3	2.5	Desc

But instead I get:

Code	Value 1	Value 2	Right_Value 1	Right_Value 2
1	1.1	Desc
2			2.2	Desc
3	2.5	Desc

How do I get my desired table? The data in question is much more complicated with around 15,000 rows and 200 columns. So my join is giving me 15,000 rows and 400 columns. I've also tried a union but that just gives my 30,000 rows.

If it helps the tables definitely have the same name as they were creating within Alteryx off of two other tables being joined together on different primary keys.

Thank you!

[clmc9601]

Hi @kfish,

Would this work?

 

Hope it helps!

[Qiu]

A bit different approach

[AndrewS · Accepted solution]

Hi @kfish ,

Option # 3 attached - surely we can come up with a few more!

[SeanAdams]

hey @kfish 

the way to know if you're stacking or joining is if the column structure is the same, you're generally stacking - if you want to add columns you're joining.   Think of a join as a way of enriching your data.

Simple solution here:

• Union the two data sets together to get one long list.   You'll have blanks in this
• Now you want to look for blanks across all fields at once.  To do this -
  • change the fields from columns to rows using a transpose
  • Filter out the blanks
  • Cross-tab back into the original format
• If you want to add a default value if you still have missing values - then you
  • can union to a default values list (text tool)
  • use a unique to remove duplicates