Combine the year, month, and day columns into a single, composite date field using Excel’s CONCATENATE function. Understand the importance of creating a valid “key” that can be used to join multiple data sources together using lookup functions or data models, and recognize the difference between 1-to-1 and 1-to-many relationships.
- [Voiceover] If you need to perform a lookup in Excel…based on multiple columns or criteria,…one of the simplest solutions is to combine…those fields together to create a unique text string or key.…In this case, I need to create a lookup…based on year, month, and day,…so I'll use the concatenate function…to merge all three of those into one new field,…which I can then use as my lookup value.…So I'll go ahead and open file 02_03_Weather…and just right-click column A,…Insert a new column,…and I'll call it Key,…and this is where I'll use the concatenate function.…
Note that I don't need to type out concatenate.…That would work, but I prefer to use the shortcut,…which is just the ampersand.…So the fields that I'm combining…are column H, ampersand, I, ampersand, and J,…year, month, and day.…So when I hit enter, as you can see,…that combines or merges all three of those fields…into one text string.…So I'll scroll down just to queue A,…make sure that it's gone through the last row.…Looks good.…
And then the next step is to open my Accident file.…
Using conditional statements and text functions like LEFT, MID, RIGHT, and CONCATENATE, you'll learn to standardize data sets from the NCDC and create new dimensions. Then find out how to use VLOOKUP to join the data, and use COUNTIF statements to analyze trends. Once the historical data is in place, Chris shows how to use Excel's powerful WEBSERVICE and FILTERXML functions to tap directly into Weather Underground's API, which provides real-time weather information for any location and allows you to estimate accident rates based on current conditions.
Last but not least, you'll learn how to build custom charts to show the expected accident risk based on real-time inputs, and walk through some additional opportunities for deeper analysis.
- Standardizing data
- Creating new data dimensions fields
- Joining data
- Analyzing trends in the data with COUNTIF
- Creating an API feed with WEBSERVICE
- Extracting components with FILTERXML
- Visualizing the findings in a custom combo chart
- Applying your new skill set