Combine Two Sets of Data with missing rows

[shawn_23]

I am trying to join two sets in the file "Boeing Stock Price" and join the two tabs Boeing stock price and Boeing Crash Dates tabs. The one set of data is boeing stock price and the second set of data is the dates of plane crashes. I am trying to join the information so that:

1. The stock price dates, that all dates are kept for stock prices for open, close, high, low stock prices and volume on all days in the boeing stock raw data set in excel. 

2. The date of a crash for Boeing is joined with the stock market data to show that "Boeing" Aircraft type and date of crash shows in a column added to the stock price data for the correct date.  

Note: Some plane crashes happen on weekends where there is not a stock price. This is a separate question but if someone could help make a workflow to have the friday stock price closing price for weekends and holidays to show this information would complete all days of the week but this is not necessary for the topic I am posting to join two sets of data. 

I am having difficulty in Alteryx where it only joins information on dates of crashes will accompanying stock market information. I am trying to see the affects of the stock price on all dates not just the crash date. 

I attached the 'Data Complete Final 3.9.23 with related tabs data, boeing stock prices raw data, and boeing crash dates', ' and 'Boeing Stock Price Alteryx with tabs Boeing Stock Price and Boeing Crash dates (these are the two tabs that I am trying to join, the other (2) documents are supporting documents for the entire project, and  'Top Gun Alteryx Workflow'  

[EkatLol]

Maybe this is a good starting point - this workflow takes all the stock dates and matches based on crash date (but keeps every stock date). The nulls in the output are days where there was not a crash but the stock market was open.

The top of the workflow looks for Saturdays and Sundays and backtracks depending on what weekend day it is (1 day for Saturday and 2 days for Sunday). There are still a few dates left over from holidays and 9/11. Let me know if it helps :)

Spoiler

[shawn_23]

That is a good start I should hopefully be able to finish it from here. Thank you for the help. 

[Yoshiro_Fujimori]

@shawn_23 ,

> I am having difficulty in Alteryx where it only joins information on dates of crashes will accompanying stock market information. 

Join tool has three outputs (Left / Join / Right).

If you want to do "Left Join", you may use "Union" tool to combine "Left" and "Join".

In the same way, you may generate a list of dates in a period (including weekends), and "Left Join" them to get the weekend 

To fill in values for weekends, you may use Transpose and CrossTab to process all the value fields in one Multi-Row Formula.

Does it work for you?