Sometimes, you may have data that you want to add to every single row in a table. For example, you might have aggregated the sum of your sales figures to create “total sales” and want to add this in as an additional column such as in the example below.
This is our starting data:
Aggregation of “Data Type” to create “Total sales”:
This creates a table with a single column (which we can rename to “Total sales”) and a single row. Now we need to add this to every single row in our original table.
In some tools this function is called an append, although Tableau Prep doesn’t have a dedicated function to perform this. However, we can do so quite simply with the following method:
Before we begin:
You would typically only want to do this if our aggregated data is a single row, otherwise you will multiply the number of rows in your initial dataset by the number of rows in your aggregate table. If you have multiple rows in your aggregate table then I would strongly consider pivoting them into as few rows as possible (See my other blog to learn how to do this!).
Step 1.)
Create a calculated field in your main table (let’s call it “Append”) which simply contains the number 1 (or anything you like really, so long as it’s the same for every row).
This should make your table look like this:
Step 2.)
Do the exact same thing for your aggregated table, meaning that we have a new Append column containing the value 1.
Step 3.)
Perform a join between the two tables, using the append column as the join clause
Giving you a table that looks like this:
Voila! You’ve now added your aggregated value to every single row in the dataset. You can now use that value for further calculations, or output the data with aggregations attached.
Final flow:
