2022: Week 44 - Solution

Solution by Tom Prowse and you can download the workflow here.

The challenge this week is looking at creating unique identifiers by combining fields and creating them with calculations.

Step 1 - Merge Order Dates

After inputting our data we want to ensure that we have a since column for our order date. Currently, this is split over three different fields so we want to merge these together. 

First we need to ensure that all of the fields are a Date. This will allow us to then merge the fields selecting all three (holding down ctrl if you are using a Windows machine) then right click and select 'Merge' 

After the merging our table will look like this: 

Step 2 - Customer Initials

We can now start to create the unique identifier using the fields that we have in our data set. The unique identifier is made up of: 

- Customer Initials

- Order Number

- 0's in between

First we can create the initials. As we have a single field that contains both First and Last name we first need to split these in separate fields (1 for First name, 1 for Last name). 

We can use an automatic split for this:

Now we have the separated fields, we can use string calculations to combine the first initials from each: 

Customer Initials 
LEFT([Customer - Split 1],1)+LEFT([Customer - Split 2],1)

After this calculation we can remove the split fields so that our table looks like this: 

Step 3 - Order Number

The next part of our unique identifier is to create the padded order number. This will require us to create a field that is 6 digits long with the order number at the end and 0s making up the rest of the digits. 

This process is called padding, and we can achieve this with the following calculation: 

Padded Order Number

RIGHT(

'000000000'+STR([Order Number])

,6)

This allows us to first add all of the 0s, then limit the number of digits by using the Right function. As a result we get a 6 digit field that can be used as our padded order number:

Step 4- Order ID

The final step is to create the complete Order ID by combining the fields that we have just created. The order ID calculation looks like this: 

Order ID
[Customer Initials]+[Padded Order Number]

After this calculation we can remove the additional fields and our final output is ready: 

You can download the full output here. 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!