Afternoon,
So I am trying to blend two data sources. All of my relationships work and return the values that I want except for the date fields that I am trying to use.
Data source 1's data type is date. So the values are "traditional dates": 1/1/2018, 5/9/2018, 12/25/2019, etc.
Data source 2 is where the challenge comes in. This data source is made up from an MS Access database which reads some spreadsheets and transforms the spreadsheet data into a table.
- There are two fields that make up the date.
- MONTH (Data Type = String)
- YEAR (Data Type = String)
My first attempt, I tried to make a date field by just concatenating the MONTH and YEAR = [MONTH]+'/'+[YEAR]. The problem with this is when I tried to make a relationship - Tableau would not give me the carrot to join on the individual date parts.
My second attempt, I extracted my data and used the MAKEDATE Function = MAKEDATE(INT([YEAR]),INT([MONTH]),1). This seems to work as I was able to make a relationship using the MY Date Part.
My question is really this - is my approach sound in this case or is there something I am not considering or a better way to build a mouse trap.
Another question - is there a way to make the date in MS Access back at the database level? I have done some research but not found a viable solution.
And last - the reason I am not joining the data and using a blend is because one data set is a big union query and this would turn into a really messy many to many situation. Blending is the best option in this case.
Thank you in advance for your help!
Scott
Your approach of using the MAKEDATE function to derive the date using the year and month is absolutely sound.
There may surely be a way to make the date in MS Access itself, but, you are able to manage without it. So, I would suggest you to continue with the solution you have at hand.
