2021: Week 35 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Split Widths & Lengths
First we need to split apart the width and lengths for both the frames and picture sizes. The sizes are a mixture of different formats and measurements so let focus on the Pictures table first.
Within the pictures table we have all of the measurements in CMs so we don't need to make an conversions. However, we do have one measurement which is a square (24cm2) so this has 24cm as the height and width.
To deal with this, we can first we want to split length from the start of the string. There isn't a clear separator to do this easily, however using the Automatic split will give us the required numbers that we need from either side of the 'x'.
This provides us with a separate field for both the height and width fields.
As you can see, the square frames only contain a length and the width field remains empty, therefore we can use a calculation to fill this missing width:
Width
IF [Width]=''
THEN [Length]
ELSE [Width]
END
We can then change both the Length and Width to a whole number and remove the Size field so our Pictures table now looks like this:
Now we have split apart the Pictures, we can do a similar process but with the Frames table. On a separate branch, input the Frames table but this time we are going to use a custom split on 'x' as a separator:
After the split we should now have a Width and Length field, but some further cleaning is required.
This time there are measurements in both cm and inches, therefore we need to convert the inches into cm by first classifying what measurement is being used:
Inches/CM
IF CONTAINS([Size],'cm')
THEN 'cm'
ELSE 'inches'
END
Then using a custom split to return the first numbers before the cm.
Then we can remove any punctuation (using the clean options in Tableau Prep) from the newly split field so that we are left with our new length field. From here we can convert the length in inches to cm using the following calculation:
Length
IF [Inches/cm]='cm'
THEN [Length]
ELSE [Length]*2.54
END
Remember an inch is 2.54cm
For the width, we can remove all of the punctuation and letters from the values and then using a similar calculation as earlier to fill in the missing values:
Width
IF [Width]=''
THEN [Length]
ELSE float([Width])
END
And then finally convert the inches into cm:
Width
IF [Inches/cm]='cm'
THEN [Width]
ELSE [Width]*2.54
END
Our Frames table should now look like this:
Step 2 - Min & Max Sides
As the frames can be rotated we need to find out what the min and max side is for each picture and frame. Therefore for each of our branches we need to determine what the min and max is using the following calculations:
Min Side
MIN([Length],[Width])
Max Side
MAX([Length],[Width])
We can repeat this same calculation on both branches so our tables should now look like this:
Pictures:
Frames:
Step 3 - Picture Area
To determine what picture will fit in each frame, we need to find the area for each. To do this we just need to multiply the min and max sides on each of the Frames and Pictures branches:
Area
[Min Side]*[Max Side]
Again both tables should now look like this:
Pictures:
Frames:
Step 4 - Match the Pictures to Frames
The final step is to match the pictures with the frames that have the smallest excess. To do this we first need to join the Pictures table with the Frames table using these conditions:
Now the tables are joined together, we can calculate the excess between each of the two areas.
Excess
[Area-1]-[Area]
We want to identify the smallest amount of excess within each frame/picture combination, therefore we can use a Fixed LOD to find the minimum for each picture:
Min Excess
Now we know the minimum excess for each picture, we can use a filter to eliminate any frames that don't have the smallest excess by using a filter calculation - [Min Excess]=[Excess].
The final steps are to remove any unneeded fields and then rename Size to Frame and we have our desired output:
The next time you're doing some household DIY remember that Tableau Prep could potentially make your life that little bit easier!
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!
