Joining? multiple tables with date table

[topazz11]

I have 3 tables and I'd like to combine them with the date table.

Sample data is attached. 

Thank you,

[DavidSkaife]

Hi @topazz11 

Here is a way that gets them to combine with the date table, and accounts for no matches to the date table as well - if i've interpreted your requirements correctly!

If you solely want it to match to the date table and don't care about the dates that have no corresponding data, then everything after the first join can be ignored (up to the sort & imputation tools)

[topazz11]

it is great. Thank you again.

How do I make it to monthly, not daily? 

Also, is there a way to generate the date table in Alteryx? or should I use spreadsheet for the date table?

[DerikSadowski]

Here is my approach. It is a little different than DavidSkaife. But it is giving me similar results to what I thought you were asking for.

[DavidSkaife · Accepted solution]

Hi @topazz11 

You can generate the date table in Alteryx, but you'd need to define your start and end dates first. The 'Generate Rows' tool is kind of doing that based on the date table, but you could use the data in the original tables instead. I've added in options to the workflow to take that approach, and added in the third option for generating the table using a defined start and end date.

When you say monthly, do you mean combine all 'Apple' to a monthly value etc?

EDIT: Assumed you mean as per the above, so added an additional step into the workflow

[topazz11]

question in this workflow.

can we convert the date to this format like..2020-01-01, 2020-02-01, 2020-03-01, 2020-04-01??

[DavidSkaife · Accepted solution]

Hi @topazz11 

Apologies, i've ran on the assumption the date was in day/month/year format, when on a second look it's month/day/year!

Simply change the formula (in both tools that convert the date to date format otherwise the join will not work) to:

DateTimeParse([Date],'%m/%d/%Y')

Which will then display it like so: