1-3f
0 (0 Likes / 0 Dislikes)
If you want to create a new column
based on the values in another one,
you can use the conditional column option here in your add column tab.
So to create a new column, it allows you to kind of create if, then, else type syntax
to choose what the output should be.
So I'm going to give it a new column name.
The scenario here actually is that
we've got some ZIP codes.
In Microsoft, we're based in 98052,
which is in Redmond, Washington, and so we're going to create a new column
to determine whether or not our sales were to
a local customer or a regional customer or somewhere else based on their ZIP code.
So this is going to be, maybe I'll call it customer location.
So we're going to start to fill out the fields across each of these rules
and then build up this if, then, else type statement.
So we're going to say, well, if our ZIP code starts
with 980, then we know they're really local.
They've got to be within a few miles of us.
So we'll call them local.
And I can choose whether I want to type in a value to use at the output,
use the value of another column, or even a parameter that might've been added to this query.
So, first that will get evaluated.
And if it doesn't match that ZIP code,
then we'll run the next one.
So else if our ZIP code begins with just 98,
then we'll say okay, it's in a rough region.
Otherwise, we'll call our customer an other customer.
So the order of these rules is important.
They'll get evaluated in this order.
And you can use this dot, dot, dot menu on the right hand side
to reorder these—move them up and down if you get that wrong.
So here we'll check the ZIP code, if it matches this, then they'll be local.
Otherwise, if it matches this, then they'll be regional.
Otherwise, we'll classify them as other.
So I'll hit okay, and then you'll see now this is starting to operate on each of the columns here.
So obviously all of these ones over in France,
they are marked as other.
Lets just filter this down
just to show our local and regional customers are being identified properly.
So our local customers—actually, there's a ZIP code over in France that matches that as well.
So maybe we need to add some more rules to say
if a ZIP code is 98 and the country is not France,
but otherwise, you can see the ones in Washington and the US are being marked as regional.
And we'll see our further local customers from particular ZIP codes nearby further down in the list.
So I can use this conditional column to do things like tagging customers in different ways,
or changing values based on grouping values together or bending values
if you wanted to say people were in a particular high sales amount
over a certain amount, I could use that conditional column to do that.
There is a whole bunch of different ways that you might use this.
And it helps by just giving you that nice, easy,
simple UX for building these rules and for managing
the order in which those rules get evaluated
and the values against which they get checked.
And that's it!