How to convert a shipping column to a row item?

We often come across sales data that record shipping as a separate column. See this dataset for example:

sample data

Fig. 202 Sample sales dataset

The challenge with such datasets is that they record distinct shipping charges for the same order ID when it’s only been charged once each unique order ID. As a result, it can be hard to calculate the correct total per order.

This wouldn’t have been a problem if shipping charges were recorded as a row item against each unique order ID - once. Like this:

sample data

Fig. 203 Final result

And that’s exactly what we are trying to achieve today with Mammoth.

So let’s get straight in.

Our Goal:

To record shipping as a single row item against each unique order ID. Here’s how we can go about it:

sample data

Fig. 204 Solution Workflow

Breaking our goal into smaller to-dos would look something like this:

  • First, we will Branch out the original dataset without the shipping column. This is crucial as we are going to merge our crafted rows into this dataset.

  • Next, we’ll try to craft the rows, i.e., OrderID-ShippingValue-Shipping. We need to convert multiple shipping rows into one. For this, we will use the “Group & Aggregate” function to calculate Max aggregated value for the Shipping column grouped by OrderID.

  • Now, we will introduce a new column and fill the column with value “shipping” with the Label & Insert function.

  • Now, let’s branch out this View into the first dataset. Map the columns into it.

  • Finally, sort the resultant data in the branch out dataset to get the desired result.

  • The final result will look something like this:

sample data

Fig. 205 Final result