easiest way to multijoin

[DADE]

I have my data in the following format in 1 excel sheet, and my idea was to put the data in different excel sheets read them in separately and then use a multi join, but the problem is that the dates appear in different columns. Let me explain:

I have 25 of these tables (for this example I will just show 2):

date	value
01/01/2016	50
01/05/2016	100
01/10/2016	200
01/12/2016	400

date2	value2
01/05/2016	5
01/08/2016	10
05/09/2016	20
01/10/2016	40

after the multi join the data looks like this (in reality the columns go until date25 and value25)

date	value	date2	value2
01/01/2016	50	NULL	NULL
01/05/2016	100	01/05/2016	5
NULL	NULL	01/08/2016	10
NULL	NULL	05/09/2016	20
01/10/2016	200	01/10/2016	40
01/12/2016	400	NULL	NULL

how do I join all the date columns while leaving the value columns as they are? The final result should be the following (until column value25):

Date	value	value2
01/01/2016	50	NULL
01/05/2016	100	5
01/08/2016	NULL	10
05/09/2016	NULL	20
01/10/2016	200	40
01/12/2016	400	NULL

Thank you for your help!

[neilgallen · Accepted solution]

The fundamental problem you have is that all your dates are not present in every table. Because of this, that's why you get the null rows in the first date filed.

A little manipulation is required to create one 'master' date table, then join at least one of your tables to that master table to give you a starting point for the multijoin.

See attached workflow. Messy, but gets the point across.

[jarrod · Accepted solution]

Hi @DADE ,

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

@neilgallen has a correct answer and mentioned it being messy, so here's a very slightly modified version:

the idea is that you union all Date fields and unique (or group by) and use that as you input 1 date. this serves as a master date list so you know you have all available iterations from all sources you are going to join. then in the join multiple tool you can drop the other date fields.

As the original author, you also have the ability to mark replies as solutions! Going forward, I’d encourage you to identify the solution or solutions that helped you solve your problem, as it's a big help to other community members. Learn more about Accepted Solutions here.

Thank you!