How to Create Group Record IDs in Alteryx

SPEAKERS
Thales Donizeti

Audio Duration: 10:00

0:00
Hello there. My name is Thales Donizeti and welcome back to another Data in the Wild episode, hosted by Data Meaning. Before we begin, don’t forget to subscribe to the channel below and click the bell to turn notifications to be the first to know every time we upload a new video.

0:33
Hey everyone. So today, what I’m going to show you is a really quick tip for those who are starting with Alteryx. And it’s how to create a group record ID using preparation? Because what we have here in audiences is regulated to which you would create basically a unique ID for your data set. Basically, the key for your data set but it doesn’t move by a specific field. So how would we go by doing that by creating a list of keys through a field?

Building Unique IDs

1:13
Like for example, if we had, you wanted to create like an ID for every city in every state, like I want an ID for the citizens in Alabama, then I want an ID for cities in Georgia and Indiana. So, I’ll create multiple ID entries for each state. So, we wouldn’t be able to do that using the record ID what other tool we can use without it?

1:45
So, one of the tools that are actually underestimated, and most people don’t even know what it does? It’s just the title. It basically creates titles of, it divides your dataset in titles. So there are a couple of methods that you can use, but we’re going to focus on the unique value. And the unique value method, we’ll do that basically, split your data set into tiles based on a unique value that you find.

Defining Your Values

2:18 So the unique value that I’m gonna define here is state. And I can group by any columns, because state is already what I’m looking for here, what I’m looking for to split my dataset to create my titles. So I’m gonna just add a browse tool here so I can visualize it better. And when I run this, you can see that what it does, it just basically enumerates that title. So like, this first state Alabama, we’re talking about that specific state so it gives that a unique value Alabama so it’s the first one.

3:03
And it basically creates a sequence number, which will be our ID that we’re looking for. So it basically creates an ID for every city in Alabama. Okay, and then what it does, it starts over if we go and look for, like the next state, and we can see Alaska, when it goes to Alaska, it starts over again. So start creating ID for that state, and so on for every state here. So this is a good way to create unique IDs, for specific if you are grouping by a specific using the title.

3:49
There will be another way, using the multi-row formula tool. I’m going to go over really quickly here so we can also understand but I think this one is more flexible, but it also takes a little bit more of a logic, thinking and using the formulas to make it work. Okay, so the Multi-row formula tool, we will do here is to create the new field called ID. And what we would do basically is we could use a formula to add up values. And basically start over every time we find a different value. That’s what we’re doing when we’re creating those IDs.

4:39
So basically, what we would do is we would go over by saying, row minus one of ID, if row minus one ID equals… Actually, row minus one state is not equal to state, then what we’re going to do? Then we’re gonna add up one, two. We’re gonna leave it as one. Else, we’re going to add up these IDs, and we are gonna close this. So that’s what we do here. And if we run this, you see it has the same effect.

5:46
Basically, let’s create an ID for every city in Alabama. But when do you find there’s a different state like Alaska, and it starts over here, that’s because the state was already sorted. If it wasn’t, we need to sort that state field. And basically do that because it basically it’s going to take consideration, the position of the row. So that’s something that you need to also look at. So that that was my way of explaining group record IDs in Alteryx. And I hope you enjoyed it, and it will be valuable for you. Thank you.

6:42
If you have any questions or suggestions for future videos, please comment below. Don’t forget to subscribe so you know when future videos are posted. Thanks for watching.