Hi all, I am new to alteryx and I have two data tables shown below, vendor and sales. datevendorexpense1/1/2001A101/1/2001B102/1/2001A20 datesales1/1/20011002/1/2001150 I need the following output .Can someone please guide me which tools I need to use to get the desired result? datevendorsalesexpense1/1/2001a100101/1/2001b100102/1/2001a150202/1/2001b1500

help with joining records

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

Join

Accepted answers

JohnJPS

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.

JoinAllUniqueExample.yxmd

All comments

JohnJPS

Hi @prravich,

Hope that helps.

JoinAllUniqueExample.yxmd

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.

joins.yxmd

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.

Joining Records.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335