Tableau Prep for Data Analysis & Business Intelligence

1. Open Tableau Prep Builder and connect to the “World Happiness – 2020.csv”Union the 2020 – 2015 files together from the homework 1 folder INSIDE your input table

2. Note: Do you need to use a matching pattern for these csv files? Can this be done in multiple ways?

3. Update your sample settings to include all data in the datasets

4. Include ONLY those fieldslisted below in the flow:

   • Country

   • Region

   • Happiness Rank

   • Happiness Score

   • Overall Rank

   • Ladder Score

   • File Paths

5. Change “Happiness Rank” to String Type

6. Rename “Overall Rank” to “Overall Happiness Rank”

7. Filter the data to include only records where the “Happiness Score” is greater than 7

8. Refresh ALL data sets in the flow to ensure all data is up to date

1) Open Tableau Prep Builder and connect to the “indian_food.csv”

2) Add a clean step to the flow using the automatic prompt (manual if version does not support)

3) Find the exact number of rows and fields in the data set. (answer – 255 rows, 9 fields)

4) Find the number of states that the dishes originate from. (answer – 25 States)

5)Examine the field types found in the dataset. How many string fields are there? (answer – 7 Fields)

6) Look at the Prep Time and Cook Time value distributions. Using the detail view, what is the most common prep time and cook time? (answer – Prep Time: 10 Minutes, Cook Time: 30 Minutes)

7)Using the search function, find the dish that has lime in it (Sai’s favorite is lime). Which dish is it? (answer – Petha)

8)Sort region field descending – which is the region with the most dishes? (answer – West)

9) Move the Prep Time and Cook Time fields to the right-most fields in the dataset.

10) Using highlighting, which state has the most “sweet” flavor profile dishes? (answer – West Bengal)

11)Filter the data down to Sai’s preferences based on the below:

• Use Keep Only to limit the data to desserts only

• Use Select Values filter to limit the data to Bihar, Odisha, and West Bengal states only

• Use a calculation filter to limit the data to dishes that take 25 minutes or less to cook

• What are the remaining dishes that Sai will use for his initial foodtruck menu? (answer – Balu shahi, Cheera Doi, Phirni, Sandesh)

1) Open Tableau Prep Builder and connect to the “OSMI 2019 Mental Health in Tech Survey Results” dataset (CSV File)

2) Add clean step to the flow > Swap to list view to review the column names (questions) and their responses and swap back to the profile pane once complete.

3) Update the “What country do you *live*” in to be uppercase format.

4) Group the “Do you *currently* have a mental disorder*” field values “Yes” and “Possibly” into “Probably” and “Don’t Know” and “Know” into “Probably Not”.

5) Split the “How many employees does your company or organization have?” into two columns. Rename the split 1 to “Min Employees” and split 2 to “Max Employees”.

6) Edit the values with “N/A” in them in the “Were you aware of the options for mental health care provided by your previous employers?” column to be“NA – Not Aware” and “NA – Not Offered”.

7) Update the “Overall, how much importance does your employer place on physical health?” column to be String Type.

8) Assign a Country/Region data role to the “What country do you *work* in?” field. Which entry does not match the data role? (Hint – Other)

9) Remove the “What is your race?” field.

1. Open flow named “Combining and Pivoting_Homework_Start”

2. Union results_1, results_2, results_3 using the union step

• Merge any mismatched field(s)

3. Join Races table to the union using Race ID so that all data from the union remains and only data where the Race IDs match from the races table are brought in (hint – it’s the one of the most common joins)

• Fix any mismatches so that the join executes (hint, look at the Field Type of your join clauses and look for any extra letters/characters in mismatches)

• Remove any fields with “-1” as they are duplicates

4. Join Drivers table to the latest join in the flow using Driver ID where only matching results are returned from both tables. (hint – this is the default join Tableau Prep uses)

• Remove any fields with “-1” as they are duplicates

• Add a clean step (this will be for the main consolidated source)

5. Add a columns-to-rows Pivot Step after the Drivers Table join (not the clean step) using the wildcard pivot option. Use the search term “fast” to bring in all measures with “fast in the name and perform the pivot. Rename the pivot fields to be “Fastest Measure Name” and “Fastest Measure Value”

• Convert Fastest Lap to DateTime

• Convert Fastest Lap speed to Number (decimal)

• Convert Fastest Lap Time to DateTime

6. Add an Aggregate Step that will group the driver surname and aggregate the fastest lap speed (max type aggregation).

• Hint: You may need to filter to a particular metric because of the pivot that we’ve added in the prior step

• We are trying to answer the question “What is the fastest lap ever by driver?”

7. Make all of the joins and union steps “Gray” in color. Add a description to the pivot step that says “Pivot Fastest Measures”. Add a description to the clean step that says “Master consolidated source”. Group steps from “Results Union” all the way through “Drivers Join” and compress the group (enable groups if warning appears).

1) Open flow “Sharing and Updating_Homework_Start”

2) Save the flow as an unpackaged flow file (.tfl). Save the flow as a packaged flow file (.tflx).

3) From the aggregation step, preview the flow results in Tableau Desktop.

4) Output the data to the following (each output should have its own branch)

• Hyper extract with create setting (name output “Hyper Create”)

• Hyper extract with append setting (name output “Hyper Append”)

• CSV with create setting (name output “CSV Create”)