Use of If-Then-Else logic in Aperture Data Studio
Imagine you are asked to use Aperture Data Studio to generate a new field for your sales data.
You have the input fields: Discount Code, Quantity and Price.
The ask is to generate a new field “Offer Price” using the following logic:
How can you do this in Aperture Data Studio? The answer is to build a custom transformation and use the “If Then Else” function.
The If Then Else function is very powerful. However, I have found it can be a bit confusing for people who encounter it for the first time. This article explains how the function works and also provides some suggestions for how to best use it.
How Does the If Then Else Function Work?
The function is below:
There are a few important points to understand about using this function.
- When you first drag the function on to the canvas it will show 3 inputs. These are “Condition”, “Condition met result” and “Else”.
- You can add additional elements by clicking on the “+” symbol. This adds an additional “Condition” and “Condition met result”. (In the example above, I have done this twice. So there are 3 pairs of “Condition” and “Condition met result”.)
- The input to the “Condition” must be a Boolean (true/false) value.
- The function is executed from the top to the bottom. The process is like this:
- The first “Condition” is checked. If it is True, the value in “Condition met result” is output and the function then stops (for this row).
- If the first “Condition” is not True, then it drops down and checks the second “Condition”. If this is True, the value in the following “Condition met result” is output and the function stops.
- This continues for any subsequent Conditions.
- If none of the Conditions are True, then the value in “Else” is output and the function stops.
In summary the If Then Else function checks a set of conditions, in a specific sequence, to determine if any are true. When a condition is found to be true, the related result is output. If none are true, the “else” value is output.
Some of you will be familiar with implementing this type of logic using programming languages. Either as an “If then else” statement or “case” statement. I have found that, if you are used to this, then you may be confused by Aperture’s approach. This is because, with a programming language, the logic will look something like this:
IF
<logic to describe the IF condition>
THEN
<definition of THEN output>
ELSE
<definition of ELSE output>
The point is that the IF logic is defined after the IF statement.
With Aperture this works the other way round. You have to create the IF logic first. You then feed this into the If Then Else function (as a Boolean). Hopefully, once you understand this difference you will find it easier to use.
Best Practice
If then else logic can be confusing (irrespective of how it’s implemented) particularly when it gets complicated. It’s the sort of thing that can make sense when you first build it and then you find it difficultto understand when you go back to it. Even worse if you are trying to untangle logic someone else has built.
So how can we implement this logic in ways that make it easier to understand and test?
Scenario 1:
Let’s consider the task mentioned at the top of this article.
Below is a screenshot of the logic built using the If Then Else logic.
This will work. However (especially given we are only evaluating 2 conditions) it’s quite complex.
Another issue is if we use the preview function, we get this:
This evaluates the logic for the first few rows. This is OK, however we see only the inputs and the output. If the result was not correct it could be hard to understand where the fault is as we don’t see any intermediate values.
Can we do better (Scenario 2)?
Here’s another way of building the same logic:
Here I’ve used the concept of variables in Functions. The logic to the left of the screen evaluates 3 variables. These are:
- “Discount Code= H and Quantity>100”
- Discount Code= H and Quantity>50 and Quantity <= 100
- Price * Quantity
This makes the core If Then Else logic much simpler. It is just this:
The other advantage relates to the preview.
We see, in the preview, the calculated values of each variable. This makes it much easier to see the outputs of the intermediate steps. They are displayed even if you haven’t completed all of the function:
This is great for checking the logic as we build it.
Can we do better still (Scenario 3)?
I think so. With the examples above we are putting all the logic in one function. There’s really no need to do this. We could split out some of the logic to be make it easier to understand and test.
Consider this Workflow:
First of all, we are using the new Workflow notes capability to document the logic. That’s really useful for maintainability.
Secondly, we have split the logic over 2 Transform Steps. The first one calculates the variables that will be used by the If Then Else logic which is in the second Transform Step.
If we show the results for the first Transform Step, we get this:
We can now see the pre-calculated variables in the grid. If we wish, we could use the Explore capability to analyse these results in more detail to test we are getting the results we expect.
If we look at the second Transform Step there is only one function, which is this:
Much simpler!
Summary
The IF-Then-Else logic in Aperture is very powerful. However, you can easily end up building very complex functions which are hard to understand and test.
Simplify the logic by:
- Using Function variables (you can make these re-usable functions, if it’s something you will use frequently)
- Splitting up logic over different functions and different Transform Steps.
If you'd like to explore my examples yourself, the above Workflows and demo data, used in these scenarios, can be imported into Aperture Data Studio by using the dmxd file below:
Comments
-
This is awesome @Seamus Kyle, thank you for sharing!
0
