Is it possible to transform a single row (deal level) containing a month range (start and end month) into a table details each month within the range?

[aaweis]

I have a table that lists our deals with the subscription start and end dates and total deal size($). I am trying to transform it so that for each deal, there is either:

• a row for each month the subscription is active and pull in the subscription value.  OR
• a row for each deal with columns representing each month falling between min(startdate) and max(enddate) where the month fields contain the deal value.

I am hoping that Dataprep can assist with this rather than wrestling with figuring out how to do it via SQL.

Attaching two screenshots to provide a visual of what I am trying to get to. Either option would be fine but the wider version is preferable (one row per deal with columns for each month deals were active.

TIA

[Trifacta_Alumni · Accepted solution]

Hi @Aaron Weis?,

We do something very similar with our subscription data! So yes, this is possible in Dataprep. Here are the high level steps:

1. Calculate the number of months between the subscription start date and the subscription end date.
2. Generate new rows for each month of the subscription. You can stop here to create the narrower output.
3. Pivot your data. This produces the wider output.

Before you begin, double-check that the "startdate" and "enddate" columns both have a date/time datatype. You need these columns to be recognized as valid date/time values in order to perform date math.

1. First, use the DATEDIF function to calculate the number of months between the values in the "startdate" column and the values in the "enddate" column. The formula to enter in your "New formula" transform will look like this:

DATEDIF(startdate, enddate, month)

Your transformer grid will look like this image:

2. In Dataprep, you use array operations to generate new rows of data. This occurs through a combination of array creation and then array flattening operations. Consequently, you need to generate a list of months that need to be added to your start date column. You will then convert that list into rows, and finally use the DATEADD function to produce the correct date values.

You can use the RANGE function to generate the list of month values. Here's how the formula will look:

RANGE(0, months + 1, 1)

This function generates a list of integer values, starting at 0, and ending at the month value that we calculated in the first step. You can see how this looks in the image below:

The list shown in the preview contains incremented numbers. These are the values that you will add to the "startdate" field to produce the subscription month.

Next, you need to convert each element in the list into a new row. To do this, click on the column that contains your list. This will generate a set of suggested transformations on the right side of the screen. Scroll through the list until you see the suggestion that reads, "Flatten array values into new rows". Click on this suggestion card. Your preview will update to display the following:

You can see that each integer is assigned to a new row. All of the other values associated with the original record are copied downwards. Click "Add" to apply this suggestion to your recipe.

Finally, use the DATEADD function to produce a column of months where the subscription was active. The formula will look like this:

DATEADD(startdate, add_to_dates, month)

This takes the integer values contained in the flattened column, and adds that number of months to the value in the "startdate" column. Your preview will look like this:

You can stop at this step to produce the narrower output.

3. To end up with the wider output, open the "Pivot" transformation. You can open the "Pivot" transformation by clicking the "Pivot" icon in the toolbar:

This opens the "Pivot columns" builder. In the "Column labels" field, enter the column containing the months of the subscription. In the "Row labels" field, enter the column containing the deal name. In the "Values" field, enter the following formula:

ANY(value)

Since the "Pivot" transformation is a two-dimensional aggregation, Dataprep expects an aggregate function to be entered in the "Values" field. The ANY function is an aggregate function that behaves as a passthrough, and inserts any value that belongs to the group defined by the values in the "Column labels" and "Row labels" fields.

Your completed builder will look like this:

You might want to do some clean-up of the resulting column names. You can use the "Rename columns" transform to find and replace the "any_value_" text. Here's how that transformation will look:

Hope this helps! If it does, please mark the answer as "Best Answer" so that other users know your question has been resolved.

[aaweis]

A thing of beauty! Thanks!