How to… create comma separated strings in Prep

The fun part of data preparation is working out the logical steps it takes to transform data from what you want, to what you need. One calculation that I came across recently really stumped me so rather than forgetting how to do this technique next time, I decided to record this as a blog post.

The technique arose from Jonathan Allenby’s excellent Preppin’ Data challenge from Week 7 in 2021. The challenge involves a lot of splitting and pivoting but the final step is to pull together a list of non-vegan ingredients. This sounds easy but isn’t with Prep’s current functionality as of version 2021.3. Here’s how the data is structured when we need to apply this technique:

Each of the columns of data has a lot of null fields where the non-vegan ingredient doesn’t exist in that product. If we tried to create a list of each of these values with comma’s separating them using just pluses in our calculation, we’ll be left with a null as Prep can calculated values containing a null.

It’s not just the nulls that are a challenge with completing this task. There are a number of solutions for concatenating string fields together in Tableau Desktop but they all use Table Calculation functions like LOOKUP and FIRST which don’t exist in Prep (at the time of writing). This isn’t just a functionality issue as Table Calculations rely on the dimension(s) used in your view to establish the correct calculation to form. As you don’t form a view in Prep, this logic doesn’t exist. Here was my logic to break this challenge down:

Handling Nulls

We need to address the nulls if we have any chance of creating a solution that gives any result containing more than just the word null. I’ve written about null values here in more detail. There are two calculation functions that are very useful in Prep to deal with null values:

• ISNULL() - assesses whether the contents of the brackets is null or not. The result of the calculation is boolean (i.e. true or false)

• IFNULL() - if the contents of the brackets is null, you can specify what you’d like to return if the value is null.

With these two functions in mind, we can start to craft the logic of what we want to happen.

The solution

We are going to make use of ISNULL() and the logical calculation function IIF(). IIF() stands for Immediate If. The function lets us test a condition and set a value for where the condition is met and where the condition isn’t met.

IIF(Condition to test, value returned if condition is met, value returned if condition is not met)

The condition we are putting into our IIF() function is whether the value in the first column being assessed is null or not. In our example, we are testing whether the egg column is null. If it is null for a row in our data, we want to return nothing. Nothing can be encoded as either two quote marks together ‘‘, or two speech marks together ””.

If the field isn’t null then we want to return the value with a comma and space ahead of it. Therefore each individual field’s calculation would look like:

IIF( ISNULL([Field]) , ’’, ’,’+[Field])

To tie all the calculations together you just need to add a + between each of the field calculations to form one large string. This results in a long calculation but really is the same test repeated for each field.

The resulting values should now look like this:

Nearly there…

You might have noticed something weird about your results of this calculation. The first value shown has a comma and space ahead of it. You could remove this from the Egg field calculation but what if there is no Egg value returned? The next value would have the comma and space ahead of it.

I got around this by just testing if the resulting string string begins with a space and comma and using the mid function to start from the character after the space if so:

Summary

Like any data preparation task, what seems really complex at first, just needs to be broken down into individual logical steps. The Prep Builder development team are great at making these tough challenges easier but they haven’t solved them all yet. Until then you are going to need to flex your preppin’ powers to solve the challenges you come across. Hope this post helps if you come across this challenge.

Happy Preppin’.