help with joining records

[prravich]

Hi all,

I am new to alteryx and I have two data tables shown below, vendor and sales.

date	vendor	expense
1/1/2001	A	10
1/1/2001	B	10
2/1/2001	A	20

date	sales
1/1/2001	100
2/1/2001	150

I need the following output .Can someone please guide me which tools I need to use to get the desired result?

date	vendor	sales	expense
1/1/2001	a	100	10
1/1/2001	b	100	10
2/1/2001	a	150	20
2/1/2001	b	150	0

[JohnJPS · Accepted solution]

Hi @prravich,

The attached should accomplish this. This uses the Unique tool and AppendFields to ensure that all vendors are present for all dates. From their the Multi-Join tool does a great job of joining in such a fashion that it provides exactly the output your going for; the final formula tool just turns null fields in from the join to zero counts.

Hope that helps.

[michael_treadwell]

 So at first glance this would just be the job of a simple join.

Join table A to table B to get you output table.

However, the complication arises when you need to generate missing data for vendor B on 2/1/2001. I've used summarise and append to do the job. I've attached a workflow showing both the simple and more complex example.

[prravich]

Thank you so much. This works great

[prravich]

Thank you Michael, this works as well

[DultonM]

@michael_treadwell and @JohnJPS....you guys are just too quick.

I've attached a workflow that is much like Michael's but has a slight reordering of the tools and a description of why each tool is there. I also converted your date field into the Alteryx date format with the DateTime tool so that you could down downstream date calculations.