New Columns
Row ID
Create an ID column in your dataset
The Row ID is pleasantly simple. It creates a new column with a ID ranging from 0 to however many rows your data set contains. You can use a Group By functionality (see the configuration guide below) to create IDs within certain groups of your data.
Configuration
The Row ID tool doesn’t have any required input. It will simply add a new Row ID column to your dataset if you just run the tool. However, you can choose to add IDs within groups.1
Additional Settings Groups
This is an optional input. Select the columns that you want to group by when creating IDs. The IDs will created inside each of these groups. Check out the example to see how it works.
When To Use
Obvoiusly the Row ID tool is helpful to get track of your data, but it empowers a couple of cools things. Such as:- Whenever you only want a subset of records within some group
- To create an ID column which can be used as the input for the Parse JSON tool
Examples
Group By: Row ID to get first two transactions per client
Group By: Row ID to get first two transactions per client
I have a some transaction data and want to find the days between the first two purchases from each of my customers
The trick to solving this in an easy way is the Row ID tool with a Group By. Notice how the ID creation starts within each of the companies. That’s enables us to filter on the ID subsequently to get all the rows where ID is less than 2 - which in our case represents the first two customer transactions
With a couple of additional tools, we get the difference in days between the first two purchases from every customer
With a couple of additional tools, we get the difference in days between the first two purchases from every customer- We use a Filter to only get the first two transactions
- We use a Compare to “move” the second purchaseDate “up” to the first transaction (we group by companyID in the Compaare tool)
- We use a Date Difference to calculate the number of days between the first and second company transactions