2020: Week 25 - Solution

June 23, 2020

You can find our full solution workflow below and download it from the Preppin' Data Community page!

This week we looked at a similar technique that we covered a couple of weeks ago in Week 20, where we looked at cipher solving and some techniques that aren't necessarily native to Prep. However, this week we are going to be converting roman numerals to normal numbers.

Step 1 - Input Numerals

The first step this week is to input the number data set, this is a single string that we need to convert from numerals to numbers. After inputting the data, we want to find out how many characters are in the string, so we use the Len function in the following calculation:

Length

Len([Number])

The length of our string is 9 characters long, so now we can add in the Scaffold data set.

After inputting the Scaffold, this is ready to join straight to our original workflow. We can do this with the following join condition:

As a result of this join, we have made our data set increase in size as we now have a row for each character (9 in total).

Now we have the additional rows, we can use the Mid function to split apart our initial string. The following calculation extracts each of the characters along the string, depending on which number is in the scaffold field:

Value

mid([Number],[Scaffold],1)

This allows us the have each of the characters on an individual row and essentially has pivoted the data for us.

Finally, we need to input our final data set that contains the Roman numeral and the numeric equivalent. Before joining this to our workflow, we need to calculate a rank:

We can then join this to our original workflow with the following join:

After the join we should have a data table that looks something like this:

Step 2 - Convert Numerals to Numbers

Now we have all of our inputs into one table, we can start to manipulate this data with some calculations in order to convert our numerals to numbers.

The first step is give our table a row number. We can use an analytical calculation for this:

Ordering

{ ORDERBY [Rank] ASC : ROW_NUMBER()}

Now we have a row number, we want to find the maximum order, for each Roman Numeral and Number. Our fixed LOD looks like this:

Max Order

After this LOD we should have a table that looks like this:

Next we want to identify how many times each Roman Numeral is repeated throughout the string. Therefore, we can again use another LOD to count the repetitions:

Repetitions

Now we have identified how many times each numeral has been repeated, we need to calculate a multiplier that is relevant to the ordering of the numerals. For this logic we are going to say if the Scaffold is less than the Ordering, and only repeated once, then -1 else 1. This will allow us to identify the 100 that we need to minus from the 500 within the string.

Multiplier

IF[Scaffold]<[Ordering] 
AND [Repetitions]=1
THEN -1 
ELSE 1
END

We now use this multiplier to calculate the total value of each numeral by multiplying by the numeric equivalent:

Numeric Equivalent

[Numeric Equivalent]*[Multiplier]

After these calculations you should now have a field that identifies how much each of the numerals is worth, depending on where they are positioned within the string.

The final step is to tidy our table, and bring all of the rows back together to give us our total output. We can use an aggregation field to do this, by grouping by Number and then aggregating the sum of numeric equivalent.

We are now ready to output our table with two fields as required:

Make sure to fill in the participation tracker, share using #PreppinData on Twitter and post you solutions onto our Tableau Forums community page so that we can compare our workflows!

Search This Blog

Preppin' Data