Tableau Prep: 010

This week 010 post is about how to use Tableau Prep to manipulate the below three tables(showing first 5 only),

email Liquid Bar Sign-up Date
dmalone1@tumblr.com 1 0 14/01/2016
dsworder6@is.gd 0 1 11/12/2016
bandresen8@rakuten.co.jp 1 1 07/11/2016
cmiskin9@dion.ne.jp 1 1 30/07/2017
tmenguyb@people.com.cn 0 1 30/10/2016
first_name last_name Date
Donielle Malone 23.04.2018
Dorry Sworder 26.12.2018
Benedict Andresen 24.12.2018
Cornell Miskin 10.12.2018
Theresita Menguy 28.08.2018
email Liquid Sales to Date Bar Sales to Date
dmalone1@tumblr.com 9380 3927
dsworder6@is.gd 8731 4300
bandresen8@rakuten.co.jp 9655 611
cmiskin9@dion.ne.jp 8371 1814
tmenguyb@people.com.cn 567 1300

to the below two formatted tables.

Status email Interested in Liquid Soap Interested In Soap Bars Sign-up Date Unsubscribe Date Liquid Sales to Date Bar Sales to Date
Resubscribed esooleyd@pcworld.com 1 0 3/12/2018 28/04/2018 6248 4716
Resubscribed mizkovicir@cyberchimps.com 1 0 26/12/2018 17/07/2018 4735 4687
Resubscribed etyres3p@archive.org 1 0 14/10/2018 8/8/2018 2225 4701
Resubscribed fcatonne40@studiopress.com 0 1 20/09/2018 20/05/2018 7244 2690
Resubscribed cguiduzzi5i@plala.or.jp 1 1 19/06/2018 24/05/2018 8056 3454
Resubscribed bmatisoff5j@flickr.com 1 0 20/12/2018 4/10/2018 4873 1186
Subscribed pvenart0@posterous.com 1 0 16/08/2018 159 1723
Subscribed erosedale2@google.com.hk 0 1 13/01/2016 2019 3452
Subscribed obowman3@hubpages.com 1 0 11/11/2018 6943 422
Subscribed fmacpadene4@behance.net 1 1 19/07/2017 8937 1569
Months before Unsubscribed group Status Interested in Liquid Soap Interested In Soap Bars email Liquid Sales to Date Bar Sales to Date
0-3 Unsubscribed 1 0 3 18419 5865
24+ Unsubscribed 1 0 13 63415 34082
Resubscribed 1 1 1 8056 3454
24+ Unsubscribed 0 1 11 56639 29523
Subscribed 0 1 34 144420 81908
6-Mar Unsubscribed 0 1 2 3927 6190
6-Mar Unsubscribed 1 0 3 19665 11449
Resubscribed 1 0 4 18081 15290
Subscribed 1 1 26 141973 64129
6-Mar Unsubscribed 1 1 5 25420 9041

On the input file, there are three tabs, [Mailing List 2018] , [Unsubscribe list], and [Customer Lifetime Value].

The idea of this exercise is to:

  • Combine these two tables [Mailing List 2018] and  [Unsubscribe list] together into one table using full join to filter for subscribed and resubscribed members 
  • Combine the subscribed and resubscribed members with this table [Customer Lifetime Value]
    • The purpose is to generate a table with the total liquid and bar sales
  • Generate a table that bin the months before unsubscribed into the following group based on the months difference between Sign-Up Date and Unsubscribed Date
    • 0 – 3
    • 3 – 6
    • 6 – 12
    • 12 – 24
    • +24 =

As shown on the below image, here are the steps to create the above output file.

Week 010 Solution Image

Below are the steps to create the above tables.

  1. Read the input file by drag and drop this table [Mailing List 2018]
  2. Add another step for data cleaning
    • Select the [email] field and click on Custom Split
      •  On Split off, select First and 1
      • On Use the separator, enter @
      • Add the Remove_last_letter field with this formula
        • left([email – Split 1],LEN([email – Split 1])-1)
        • This is to remove the last letter
        • Remove numbers by selecting Clean > Remove Numbers
        • Rename [Remove_last_letter] as JoinField
          • This is to use this field as join with the {Unsubscribe list]]
  3. Read the input file by drag and drop this table [Unsubscribe list]
  4. Add another step for data cleaning
    • Add the [First _letter] with this formula
      • lower(LEFT([first_name], 1))
        • This is to extract the first letter only
      • Remove the [First_name] field
    • Change the [Date] field as Date
    • Add the [JoinField] field with this formula
      • lower([First_Letter]+[last_name])
      • This is to use this field as join with the {Mailing List 2018]
      • Remove punctuation by selecting Clean > Remove Numbers
      • Remove all spaces by selecting Clean > Remove All Spaces
    • Remove both [First_letter] and [Last_name]
  5. Drag Clean1 to Clean2
    • On Applied Join clauses, select JoinField for both sides
    • On Join Type, make it Full Join
  6. Add another step for data cleaning
    • Remove the following both fields
      • [email-split1] and {JoinFiled-1]
    • Rename [Date] to [Unsubscribed Date]
    • Rename [Liquid] to [Interested in Soap Bars]
    • Rename [Bar] to [Interested in Soap Bars]
    • Add the [status] field with this formula
      • IF ISNULL([Unsubscribed Date]) THEN ‘Subscribed’
        ELSEIF [Sign-up Date] >= [Unsubscribed Date] THEN “Resubscribed”
        ELSE “Unsubscribed”
        END
  7. Add another step to filter for subscriber and resubscriber
    • Click on the [Status] field and select Filter Values
    • On the Filter, select both Resubscribed and Subscribed
  8. Drag the [Customer Lifetime] table to the Canvas
    • Add another step for data cleaning
      • Copy what is on the clean2 step to clean 3 steps
  9. Drag the filter: Sub n Resub step to clean 3 to inner join it based on the JoinField
  10. Add the output step to create the [Email List with Detail]
  11. To create the [Mail List Analytics] table, follow these steps
    • On the clean5 step, click to add a branch
      • On the [status] filter, make sure all selections are selected
    • Drag the [Filter All] step to [clean3] step to inner join based on the JoinField
    • Add another step for data cleaning
      • Create this field [Month Dif] for the month difference calculation
        • DATEDIFF(‘month’,[Sign-up Date],[Unsubscribed Date])
      • Add this field [Months before Unsubscribed Group] with this formula
        • IF [Month DIF] >= 24 THEN “24+”
          ELSEIF [Month DIF] >= 12 THEN “12-24”
          ELSEIF [Month DIF] >= 6 THEN “6-12”
          ELSEIF [Month DIF] >= 3 THEN “3-6”
          ELSEIF [Month DIF] >= 0 THEN “0-3”
          ELSE “”
          END
        • This will be use for grouping
    • Remove the following fields
      • [Unsubscribed Date
      • [Sign-Up Date]
      • [JoinField]
      • [JoinField-1]
      • {Month Dif]
    • Add an Aggregate step
      • Drag these fields to group fields
        • [Momths Before Unsubscribed Group]
        • {Interested in Liquid Soap]
        • Interested in Soap Bars
        • [Status]
      • Drag these fields to aggregate fields
        • [Liquid Sales to Date]
        • Bar Sales to Date
        • [meail]
          • make this as CNT, which is for count
    • add the output step to create the [Mail List Analytics]

Attached is the Week 010 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.

Tableau Prep: Week 009

This week 009 post is about how to use Tableau Prep to manipulate the below two tables(showing first 5 only),

Tweet
Hey @C&BSudsCo you suds are soap…I expected beer!
WTF?! You’re Soap has just filled my bathroom full of bubbles!! Way to bubbly for me @C&BSudsCo
What kind of moron name is @C&BSudsCo?
No where near enough bubbles from your Soap Bar @C&BSudsCo. I wanted a bar of soap, not a chocolate bar
My wife has accused me of having an affair you morons. You’ve over perfumed your Soap Bar @C&BSudsCo
Rank Word
1 the
2 of
3 to
4 and
5 a
6 in
7 is
8 it
9 you
10 that

to the below formatted table.

Word Tweet
shampoo Who thought glitter in a beard shampoo was a good idea
hate I hate liquid soap I only like Bars
bathroom WTF You’re Soap has just filled my bathroom full of bubbles Way to bubbly for me
bubbles No where near enough bubbles from your Soap Bar  I wanted a bar of soap not a chocolate bar
bubbles WTF You’re Soap has just filled my bathroom full of bubbles Way to bubbly for me
suds Hey you suds are soapI expected beer

On the input file, there are two tabs, [Complaints] and[Common English Words].

The idea of this exercise is to:

  • Combine these two tables [Complaintst] and  [Common English Words] together into one table using left join
  • Break the tweets into one field
  • Exclude the row that has common English words

As shown on the below image, here are the steps to create the above output file.

Week 009 Image

Below are the steps to create the above table.

  1. Read the input file by drag and drop this table [Complaints]
  2. Add another step for data cleaning
      • Click on the Tweet field and click on the Custom Split
        • On split off, select All
        • On User the separator, enter the dash
  3. Add a Pivot step
    • Select Columns to Rows
    • Drag and drop [Delete_Char – Split1] to [Delete_Char – Split20] to Pivoted Fields
  4. Add another step
    • Remove all these fields
      • Pivot1 Names, Tweet_Trim, Tweet_Trim-1, Tweet
    • Rename [Delete_Char] to [Tweet]
    • Rename [Delete_Char Split] to [Word]
    • Trim [Word] by select Clean > Trim Spaces
    • Select the empty space and right click to exclude it
  5. Read the input file by drag and drop this table [Common English Words]
    • Remove [Rank]
    • Trim [Word] by select Clean > Trim Spaces
  6. Drag clean4 to clean2 to left join based on the following fields
    • [Word]
  7. Add another step
    • Remove the [Word-1] field
  8. Finally, add the output step to save it as csv.

Attached is the Week 009 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.

Tableau Prep: Week 008

This week 008 post is about how to use Tableau Prep to manipulate the below two tables,

Type Action Date Quantity Store ID Crime Ref Number
Bar Theft 19/03/2019 10 OX1 S04P1
Bar Stock Adjusted 25/03/2019 -10 OX1 S04P1
Bar Theft 22/03/2019 5 OX1 S04P2
Bar Stock Adjusted 25/03/2019 -4 OX1 S04P2
Liquid Theft 02/03/2019 100 WM1 S04P3
Branch ID
OX1 – Oxford Street
WM1 – Wimbledon 1
WM2 – Wimbledon 2
ST1 – Stratford

to the below formatted table.

Branch Name Crime Ref Number Days to complete adjustment Number of Records Stock Adjusted Stock Variance Stolen volume Theft Type
Oxford Street S04P8 2 1 1/3/2019 0 105 27/02/2019 Liquid
Wimbledon 1 S04P7 1 1 1/3/2019 2 22 28/02/2019 Bar
Wimbledon 1 S04P3 0 1 2/3/2019 0 100 2/3/2019 Liquid
Wimbledon 2 S04P4 1 5 5 3/4/2019 Bar
Oxford Street S04P5 1 14 14 22/03/2019 Liquid
Oxford Street S04P1 6 1 25/03/2019 0 10 19/03/2019 Bar
Wimbledon 2 S04P6 60 1 3/4/2019 0 2 2/2/2019 Liquid
Oxford Street S04P2 3 1 25/03/2019 1 5 22/03/2019 Bar

On the input file, there are two tabs, [Theft Audit] and[Branch ID].

The idea of this exercise is to:

  • Combine these two tables [Theft Audit] and the aggregate from this [Branch ID] together into one table using inner join
  • Provide a single unique record for each Crime Ref Number
  • Calculate the Stock Variance between quantity of theft and quantity of stock adjusted
  • Calculate the Number of Days to complete adjustment

As shown on the below image, here are the steps to create the above output file.

Week 008 Image

Below are the steps to create the above table.

  1. Read the input file by drag and drop this table [Theft Audit]
  2. Add another step for data cleaning
      • On Type, Group and replace each of these two
        • Bar
        • Liquid
  3. Read the input file by drag and drop this table [Branch ID]
  4. Add another step for data cleaning
    • Click on [Branch ID] and click on Custom Split
      • On split off, select All
      • On User the separator, enter the dash
    • Rename [Branch ID – Split 2] to [Branch Name]
    • Rename [Branch ID – Split 1] to [Store ID]
    • Remove [Branch ID]
  5. Drag clean1 to clean2 to inner join based on the following fields
    • [Store ID]
  6. Add a pivot step
    • Drag Action to Pivot Fields
    • Drag Date to Pivot to Field to aggregate for new columns
      • Select Min
      • The purpose for using is to just get the minimum date
  7. Add another step
    • Remove the below fields
      • [Store ID-1] and [Store ID]
  8. Add aggregate step
    • Drag these fields to Group Fields
      • Branch Name, Crime Ref Number, Type
    • Drag these fields to Aggregate fields
      • Stock Adjusted and Theft
  9. Add another step
    • Add these two calculated fields
      • Days to complete Adjustment
        • IFNULL([Stock Adjusted]-[Theft],0)
        • This is to calculate the number of adjustment days
      • Number of Records
        • 1
  10. Finally, add the output step to save it as csv.

Attached is the Week 008 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.

Tableau Prep: Week 007

This week 007 post is about how to use Tableau Prep to manipulate the below two tables,

Ship ID Departure Date Max Weight Max Volume
Tug-05 3/27/2019 500 50
Freighter-04 3/31/2019 5000 500
Freighter-03 4/1/2019 5000 500
Freighter-01 4/6/2019 5000 500
Tanker-02 4/7/2019 1000 200
Salesperson Departure ID Date Logged Product Type Weight Allocated Volume Allocated
Daisy Steiner Tanker-05-04-05-2019 2/21/2019 Spurs Jerseys 111 10
Tim Bisley Freighter-01-18-06-2019 1/14/2019 Phone Catalogues 756 48
Daisy Steiner Tanker-04-27-04-2019 1/26/2019 Oil 220 28
Colin Chien Freighter-02-10-04-2019 3/11/2019 Rubber Ducks 536 20
Colin Chien Tanker-05-20-05-2019 1/27/2019 Vinyl Records 148 30

to the below formatted table.

Ship ID Departure Date Max Weight Max Volume Weight Allocated Volume Allocated Max Weight Exceeded? Max Volume Exceeded?
Tug-04 6/11/2019 500 50 304 41 FALSE FALSE
Tug-04 6/21/2019 500 50 37 16 FALSE FALSE
Freighter-01 6/18/2019 5000 500 4580 556 FALSE TRUE
Freighter-02 5/26/2019 5000 500 4252 294 FALSE FALSE
Tug-01 6/13/2019 500 50 270 44 FALSE FALSE

On the input file, there are two tabs, [Departure Details] and[Allocation Details].

The idea of this exercise is to:

  • Combine these two tables [Departure Details] and the aggregate from this [Allocation Details] together into one table using inner join
  • Ascertain whether the Weight Allocated is greater than Max Weight or not
  • Ascertain whether the Volume Allocated is greater than Max Volume or not

As shown on the below image, here are the steps to create the above output file.

Week 007 Image

Below are the steps to create the above table.

  1. Read the input file by drag and drop this table [Allocation Details]
  2. Add another step for data cleaning
      • Click on departure ID and select custom split
        • On the Use the separator, enter a space
        • On Slip off, select All
      • Create the [Ship ID] field with this formula
        • Trim([Departure ID – Split 1] +”-” +[Departure ID – Split 2])
        • This is to create the Ship ID for joining purpose
      • Change the type to string for the following fields
        • [Departure ID -Split 3]
        • [Departure ID -Split 4]
        • [Departure ID -Split 5]
      • Create the [Departure Date] field with this formula
        • MAKEDATE([Departure ID – Split 5], [Departure ID – Split 4], [Departure ID – Split 3])
        • This is to create  for joining purpose
    • Add another step to delete the following fields
      • [Departure ID -Split 1] to [Departure ID -Split 5]
      • [Departure ID], [Salesperson], [Date logged], [Product Type]
    • Add Aggregate
      • Drag [Departure Date] and [Ship ID] to Group Fields
      • Drag [Weight Allocated] and [Volume Allocated] to Aggregate Fields
      • The purpose to  aggregate the [Weight Allocated] and [Volume Allocated] by [Departure Date] and [Ship ID]
    • Drag and drop [Departure Detail] tab to the canvas
    •  Drag the [Departure Date] table to join the [Aggregate 1]
      • On the Applied Join Clauses, inner join [Ship ID] to [Ship ID] and [Department ID] to [Department ID] for both
    •  Add another step
      •  Create these two condition formulas
        • Max Weight Exceeded?
          • [Weight Allocated] > [Max Weight]
        • Max Volume Exceeded?
          • [Volume Allocated] > [Max Volume]
        • These two fields are just to test whether the Weight and Volume are exceeded or not
    • Add another step
      •  Delete two fields
        • [Ship ID – 1] and [Departure Date-1]
  3. Finally, add the output step to save it as csv.

Attached is the Week 007 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.

Tableau Prep: Week 006

This week 006 post is about how to use Tableau Prep to manipulate the below two tables,

Country Category City
England Bar Soap London
England Liquid Soap London
England Bar Soap Manchester
England Liquid Soap Manchester
England Bar Soap York
England Liquid Soap York
Type of Soap Manufacturing Cost per Unit Selling Price per Unit
Bar 0.8 3
Liquid 0.2 1.2

to the below formatted table.

Month Country Category Profit
Jan 19 Scotland Bar Soap 1475
Feb 19 Scotland Bar Soap 1350
Mar 19 Scotland Bar Soap 1550
Jan 19 Scotland Liquid Soap 755
Feb 19 Scotland Liquid soap 695
Mar 19 Scotland Liquid Soap 645
Jan 19 England Bar Soap 1955
Feb 19 England Bar Soap 2145
Jan 19 England Liquid Soap 1630
Feb 19 England Liquid Soap 1755
Mar 19 England Bar Soap 2079
Mar 19 England Liquid Soap 914

On the input file, there are three tabs, [England – Mar 2019], [Soap Pricing Details], and [Company Data].

The idea of this exercise is to:

  • Combine these two tables [England – Mar 2019] and [Soap Pricing Details] together
  • Create and aggregate value for March and add it to this table [Company Data]

As shown on the below image, here are the steps to create the above output file.

Week 006 Image

Below are the steps to create the above table.

  1. Read the input file by drag and drop this table [England – Mar 2019]
  2. Click on the Multiple Files and select “Wildcard union”
  3. Add another step for data cleaning
      • Click and remove all of the following fields
        • [Month], [Profit], [Type of Soap], [Manufacturing Cost per Unit], and [Selling Price per Unit]
      • Filter out the city field by adding this formula
        • NOT ISNULL([City])
      • Remove the [File Path] field
      • Click on [Table Names] field and select Custom Split
        • On the Use the separator, enter a space
        • On Slip off, select All
      • Add this [Month1] calculate field to convert month abbreviation to digit
        • CASE [Table Names – Split 2 – Split 1]
          WHEN “Jan” THEN 01
          WHEN “Feb” THEN 02
          WHEN “Mar” THEN 03
          WHEN “Apr” THEN 04
          WHEN “May” THEN 05
          WHEN “Jun” THEN 06
          WHEN “Jul” THEN 07
          WHEN “Aug” THEN 08
          WHEN “Sep” THEN 09
          WHEN “Oct” THEN 10
          WHEN “Nov” THEN 11
          WHEN “Dec” THEN 12
          END
        • The purpose of the above formula is to convert it from text to number
      • Change [Table Names – Split 2 – Split 2] to Whole number
      • Rename [Table Names – split 2 – split] to Year
        • The purpose of the above formula is to convert it from text to number
      • Create the Month field using the below formula
        • MAKEDATE([Year], [Month1], 19)
      • Remove the [Table Name]
  4. Add another step to remove the below fields
    • [Table Names- Split – Split1], [Year], [Month]
    • Drag and drop [Soap Pricing Details] to the canvas
    • Add another step
      • Add the [Category] field by add this formula
        • [Type of Soap] + ” Soap”
        • The purpose of this field is for joining with the [England – Mar 2019] table
      • Remove this field [Type of Soap]
  6. Drag and inner join both of the [England – Mar 2019] and [Soap Pricing Details] tables based on the [caetgory] field
  7. Add another step to create the following fields
    • [Sales]
      • [Units Sold] * [Selling Price per Unit]
    • [COGS]
      • [Units Sold] * [Manufacturing Cost per Unit]
    • [Profit]
      • [Sales] – [COGS]
  8. Add another step to delete the following fields
    • [category-1], [Sales], [COGS], [Manufacturing Cost Per Unit], [Selling Price per Unit], [Units Sold], and [City]
  9. Aggregate the profit based on the following fields
    1. Month, Country, Category
  10. Drag and drop [Company Date] table to the canvas
  11. Drag the [Company Data] to [aggregate 1] to add the Mar 2019 data
  12. Finally, add the output step to save it as csv.

Attached is the Week 006 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.

Tableau Prep: Week 005

This week 005 post is about how to use Tableau Prep to manipulate the below table,

Date Customer ID Notes
Monday 29439 Called about their policy #4899. Wanted to know the balance.
Tuesday 39822 Called regarding policy #4030. Change of Address requested
Tuesday 83219 Emailed about the recommendation scheme. Wants to get the bonus for Customer 23813
Tuesday 27316 Called complaining. Had to wait on the line and called back multiple times. Phone lines are too busy apparently. Gave $40 to shut them up. Added to account #3001
Wednesday 12219 Email about #2001. Raised a complaint.
Thursday 39822 Called about policy #4030. Gave the wrong postcode! Jeez!
Thursday 49291 Email asking to Change Address. Change made to policy #9220
Friday 40201 Emailed requesting a new savings account. Policy created #6090

to the below format, which also shows the first 5 rows.

Statement? True Date Balance? Complaint? Policy Number Customer ID
0 6/17/2019 1 0 4899 29439
0 6/18/2019 0 0 4030 39822
0 6/18/2019 0 0 3001 27316
0 6/19/2019 0 0 2001 12219
0 6/20/2019 0 0 4030 39822

On the input file, there are two tabs, [Week commencing 17th June 19] and [Week commencing 24th June 19].

It does not have to be just two tabs. It could be more tabs as long as the table format and the tab name follow the same format. Tableau Prep will be able to automatically pick it up.

The idea of this exercise is to:

  • Create a new field derive from the date on the tab name
  • Create the following three additional fields based on the following key words
    • Statement
    • Balance
    • Complaint
  • Filter out any rows without any policy number

As shown on the below image, here are the steps to create the above output file.

Week 005 Solution

Below are the steps to create the above table.

  1. Read the input file by drag and drop or click File > Open
  2. Click on the Multiple Files and select “Wildcard union”
  3. Add another step for Date
      • Click on the Table Names field to select Custom Split
        • On separator, click on the text area and hit the space bar
        • On Split off, select “All”
      • Click on [Table Names – Split 3] and select Create Calculate Fields to create a Day field
        • Add this formula
          • REPLACE([Table Names – Split 3],”th”, “”)
          • The purpose is to get right of the “th”
          • Change the format to whole number
      • Create the Month field by adding the below formula
        • CASE [Table Names – Split 4]
          WHEN “January” THEN 1
          WHEN “February” THEN 2
          WHEN “March” THEN 3
          WHEN “April” THEN 4
          WHEN “May” THEN 5
          WHEN “June” THEN 6
          WHEN “July” THEN 7
          WHEN “August” THEN 8
          WHEN “September” THEN 9
          WHEN “October” THEN 10
          WHEN “November” THEN 11
          WHEN “December” THEN 12
          END
        • The purpose of the above formula is to convert it from text to number
      • Create the Year field by adding the below formula
        • (“20” + [Table Names – Split 5])
        • Change the format to whole number
      • Create a new Date1 to convert the Day of the Week to digit instead by using the below formula
        • CASE [Date]
          WHEN “Monday” THEN 0
          WHEN “Tuesday” THEN 1
          WHEN “Wednesday” THEN 2
          WHEN “Thursday” THEN 3
          WHEN “Friday” THEN 4
          WHEN “Saturday” THEN 5
          WHEN “Sunday” THEN 6
          END
        • The purpose of convert to digit is to use on the MakeDate function to increment the date base on the starting day of the week
      • Create a True date from combining Year, Month, Day using the MakeDate function
        • MAKEDATE([Year], [Month], [Day]+[Date1])
      • Add another step to delete all of the following fields
        • Table Names-Split 1 to Table Names – Split 5
        • Year, Month, Day, Date1, and Date
        • File Paths, Table Names
        • Change Customer ID to String/text
      • Add another step to create the following fields
        • Policy Number
          • Add this formula
          • REGEXP_EXTRACT([Notes], ‘#(\d+)’)
          • This is to extract the number starts with the pound sign
        • Statement
          • Add this formula
          • If REGEXP_MATCH([Notes], ‘\State(m|men|ment)’)
            THEN 1
            ELSE 0
            END
          • This is to check whether the keyword ‘Statement’ is on the Notes field or not
        • Balance
          • Add this formula
          • IF REGEXP_MATCH([Notes], ‘\Balan(c|ce)’)
            THEN 1
            ELSE 0
            END
          • This is to check whether the keyword ‘Balance’ is on the Notes field or not
        • Complaint
          • Add this formula
          • IF REGEXP_MATCH([Notes], ‘\Balan(c|ce)’)
            THEN 1
            ELSE 0
            END
      • Add another step to exclude any policy number without any Policy Number
        • Filter out non policy number using the below formula
          • Not ISNULL([Policy Number])
        • Remove the [Notes] field
      • Finally, add the output step to save it as csv.

Attached are the Week 005 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.

Tableau Prep: Week 004

This week 004 post is about how to use Tableau Prep to manipulate the below table,

DATE OPPONENT RESULT W-L HI POINTS HI REBOUNDS HI ASSISTS
Fri, Oct 18 vsMinnesota W112-108 1-0 DeRozan 28 Aldridge 19 Gasol 6
Mon, Oct 21 @Portland L121-108 1-1 DeRozan 28 Aldridge 8 DeRozan 9
Wed, Oct 23 @Los Angeles W143-142 OT 2-1 Aldridge 37 Cunningham 12 DeRozan 14
Fri, Oct 25 vsIndiana L116-96 2-2 DeRozan 18 Aldridge 13 DeRozan 4
Mon, Oct 28 vsLos Angeles W110-106 3-2 DeRozan 30 DeRozan 12 DeRozan 8

to the below format, which also shows the first 5 rows.

W-L(Clean) Opponent (clean) HI ASSISTS – Player HI ASSISTS – Value HI REBOUNDS -Player HI REBOUNDS – Value HI POINTS – Player HI POINTS – Value DATE OPPONENT RESULT
 1-0 Minnesota Gasol 6 Aldridge 19 DeRozan 28 10/18/2019 vsMinnesota W112-108
 1-1 Portland DeRozan 9 Aldridge 8 DeRozan 28 10/21/2019 @Portland L121-108
 2-1 Los Angeles DeRozan 14 Cunningham 12 Aldridge 37 10/23/2019 @Los Angeles W143-142 OT
 2-2 Indiana DeRozan 4 Aldridge 13 DeRozan 18 10/25/2019 vsIndiana L116-96
 3-2 Los Angeles DeRozan 8 DeRozan 12 DeRozan 30 10/28/2019 vsLos Angeles W110-106
 4-2 Dallas DeRozan 9 Gay 11 DeRozan 34 10/30/2019 vsDallas W113-108 OT

The idea of this exercise is to

  • Split each the following three fields into two and add  “-Player” and “-Value” to each of the new split
    • HI Points
      • Hi Points – Player
      • Hi Points – Value
    • Hi Rebounds
      • Hi Rebounds -Player
      • Hi Rebounds – Value
    • Hi Assists
      • Hi Assists – Player
      • Hi Assists – Value
  • Create a clean opponent field without @ and vs

As shown on the below image, here are the steps to create the above output file.

Week 004 Solution

Below are the steps to create the above table.

  1. Read the input file by drag and drop or click File > Open
  2. Add another step for split and rename
    • Select Hi Points and click on Automatic Split
      • Click on Hi Points – Split1 to rename it to Hi Points -Player
      • Click on Hi Points – Split2 to rename it to Hi Points -Value
    • Select Hi Rebounds and click on Automatic Split
      • Click on Hi Rebounds – Split1 to rename it to Hi Rebounds -Player
      • Click on Hi Rebounds – Split2 to rename it to Hi Rebounds -Value
    • Select Hi Assists and click on Automatic Split
      • Click on Hi Assists – Split1 to rename it to Hi Assists -Player
      • Click on Hi Assists – Split2 to rename it to Hi Assists -Value
  3. Add another step to delete and replace
    • Click on all of the below fields to delete it
      • Hi Points
      • Hi Rebounds
      • Hi Assists
    • Click on the Opponent field
      • Click on the Create Calculate field
      • Add the following formula to get of the @ sign and “vs”
        • REGEXP_REPLACE([OPPONENT], ‘\@|vs’, “”)
      • Rename the new field as “Opponent (clean)”
    • Click on W-L field
      • Click on the Create Calculate field
      • Add the following formula with a space so it still be test when export to csv
        • ” ” +[W-L]
      • Rename the new field as “W-L(Clean)
      • Delete the [W-L] field
  4. Finally, add the output step to save it as csv.

Attached are the Week 004 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.

Tableau Prep: Week 003

This week 003 post is about how to use Tableau Prep to manipulate the below two tables,

Name Monthly Cost Contract Length (months) Start Date
Carl 20 24 13/12/2018
Jonathan 15 6 2/22/2019
Andy 45 12 17/10/2018
Sophie 30 12 19/11/2018

Scaffold

to the below format, which also shows the first 10 rows.

Payment Date Name Monthly Cost Contract Length (months) Start Date
10/19/2019 Sophie 30 12 11/19/2018
9/17/2019 Andy 45 12 10/17/2018
7/22/2019 Jonathan 15 6 2/22/2019
11/13/2020 Carl 20 24 12/13/2018
9/19/2019 Sophie 30 12 11/19/2018
8/17/2019 Andy 45 12 10/17/2018
6/22/2019 Jonathan 15 6 2/22/2019
10/13/2020 Carl 20 24 12/13/2018
8/19/2019 Sophie 30 12 11/19/2018
7/17/2019 Andy 45 12 10/17/2018

The idea of this exercise is to

  • Combine both tables into one single table by creating a Payment Date field
  • Create the number of payment date for each name based on the length of the cotract
  • If the contract length is 6 months for Johnathan, then there has to be an 6 rows with different payment date

As shown on the below image, here are the steps to create the above output file.

Week 003 Solution

Below are the steps to create the above table.

  1. Read the input file by drag and drop or click File > Open
  2. Drag both the Contract Details and Scaffold sheets on the Canvas
  3. Inner Join both sheets
    •  On the Applied Join Clauses under the settings, select [Contract Length (months)] to the left and [Length] to the right
    • Select the >= sign
      • The purpose of this is to exclude any rows greater than the contract length
  4. Add another step to create the Payment Date field
    • Create the Payment Date field with the following formula
      • DATEADD(‘month’, [Contract Length (months)] -[Length], [Start Date])
    • Change the format to Date
    • Remove the [Length] field since it is not needed
  5. Finally, add the output step to save it as csv.

Attached are the Week 003 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.

Tableau Prep: Week 002

This week 002 post is about how to use Tableau Prep to manipulate the below table, which shows the first 5 rows,

City Metric Measure Value Date
London Max Temperature Celsius 13 16/02/2019
London Min Temperature Celsius 6 16/02/2019
Londen Precipitation mm 2 16/02/2019
London Wind Speed mph 7 16/02/2019
London Max Temperature Celsius 14 17/02/2019

to the below format, which also shows the first 5 rows.

City Date Max Temperature_Celsius Wind Speed_mph Precipitation_mm Min Temperature_Celsius
Edinburgh 22/02/2019 13 12 8 9
Edinburgh 20/02/2019 11 13 18 8
Edinburgh 16/02/2019 11 13 4 8
London 18/02/2019 11 10 12 4
Edinburgh 18/02/2019 9 20 11 5

The idea of this exercise is to

  • To group the City same name into one with different spellings
  • Combine the Metric & Measure fields into one new field
  • Pivot the new field from column to rows to columns
  • Sum the total for each new columns by City and Date

As shown on the below image, here are the steps to create the above output file.

Week 002 Image

Below are the steps to create the above table.

  1. Read the input file by drag and drop or click File > Open
  2. Clean the file
    • Create a new calculate file by combining the below two fields
      • Metric
      • Measure
    • Remove above both fields once the new field is creates
    • Change the format of the Value field to Whole Number
    • Filter out the City by excluding it
    • Group and Replace the different spelling variations of London into just London
    •  Group and Replace the different spelling variations of Edinburgh into just Edingburgh
  3.  Pivot the new field from column to rows to columns
    • Drag the Metric_Measure field into Pivot Fields
      • Drag the Value field into Field to aggregate for new columns
  4. Add another step to group and replace the null to zero for the following fields
    • Max Temperature_Celsius
    • Wind Speed_mph
    • Precipitation_mm
    • Min Temperature_Celsius
  5. Finally, add the output step to save it as csv.

Attached are the Week 002 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.

Tableau Prep: Week 001

This post is about how to use Tableau Prep to manipulate the below table, which shows the first 5 rows,

Dealership Red Cars Silver Cars Black Cars Blue Cars When Sold Month When Sold Year
A 451 325 377 396 1 2018
A 355 238 259 246 2 2018
A 124 456 303 175 3 2018
A 391 456 429 223 4 2018
A 139 177 241 376 5 2018

to the below format, which also shows the first 5 rows.

Total Cars Sold Date Dealership Red Cars Silver Cars Black Cars Blue Cars
1549 1/1/2018 A 451 325 377 396
1328 1/1/2018 B 235 313 363 417
1098 1/2/2018 A 355 238 259 246
777 1/2/2018 B 223 135 163 256
1221 1/3/2018 B 447 308 186 280
1058 1/3/2018 A 124 456 303 175

As shown on the below image, here are the steps to create the above output file.

Week 001 Image

  1. Read the file Week 1 Input.xlsx file by either drag and drop or click File > Open
  2. Create a new Date field by using the Makedate function by adding the following fields:
    • [When Sold Year] for Year
    • [When Sold Month] for Month
    • 1 for day
  3. Aggregate
    • Drag both Date & Dealership to group field
    • Drag the following fields to aggregate fields to sum it in no particular order
      • Red Cars
      • Black Cars
      • Silver Cars
      • Blues Cars
  4. Add another step to create the total for cars
    • Create a total cars sold calculate field with the following this formula
      • [Red Cars] + [Black Cars] + [Silver Cars] + [Blue Cars]
  5. Add the output step and click to run the RunFlow

Attached are the Week 001 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.