easiest way to multijoin

Question

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):

datevalue01/01/20165001/05/201610001/10/201620001/12/2016400

date2value201/05/2016501/08/20161005/09/20162001/10/201640

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

datevaluedate2value201/01/201650NULLNULL01/05/201610001/05/20165NULLNULL01/08/201610NULLNULL05/09/20162001/10/201620001/10/20164001/12/2016400NULLNULL

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):

Datevaluevalue201/01/201650NULL01/05/2016100501/08/2016NULL1005/09/2016NULL2001/10/20162004001/12/2016400NULL

Thank you for your help!

neilgallen · Accepted Answer

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 Answer

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!