How to consolidate multiple columns into a single column through Transpose technique in Data Recipe in CRM Analytics

Business Use case:

Here is a real-world example from a client in financial services. Date of Birth, Wedding Anniversary, and Client Anniversary are three milestones that are recorded in the actual client accounts (person accounts). Customers prefer to have a separate row with each milestone listed in the dashboard. in order to identify the months in which their customers are approaching their milestones, as well as the nature of those milestones, and then get in touch with them to wish and offer product recommendations.

Sample data

Problem Statement:

The clients want to see the Desired output like as below. Meaning all these columns have to be transposed to rows. As you know, there is no direct way to do the transpose data in data recipe.

Desired output

solution:

In data processing tasks, it’s often necessary to unpivot data from a column -wise format to a row-wise format. This transformation, known as transposing columns into rows, is a common operation in data analysis and reporting tasks. In this article, we’ll explore approaches in Data recipe to achieve this transformation.

The below are the steps involved with the transpose:

1. Loaded the data into Data Recipe
2. Created the Different streams for each column which needs to transpose into rows using filter node
3. Added Transform node for each stream after filter node and created the 2 different custom columns to have the milestone type and millstone date, included the drop nodes to drop the unnecessary columns
4. Union(append) all the different streams and created a final stream
5. Registered Output dataset from the final stream.

Let's First load the object into Data recipe.

Loading data into Data Recipe

After loading the data into data recipe, lets create a “n” number of different streams based on how many columns you wanted to transpose.

eg : In our sample data it has 3 columns, so we have to create three different streams by an adding a filter node for each respective column

Adding filters

We have included respective column is not null filter logic in the filter node, because we don't want to include the null records in the stream.

Note: if you want to deal with null values. then, following the load node, construct a new column with null handling procedures for each column that needs to be transposed. To build a different stream, use those fields.

Let's add the transform node after the filter node to create the identifier field and transposed column/field value field for each stream.

eg : In our use case we wanted to have the Milestone type column/field to identify what kind of milestone these records are and another column/field for respective milestone dates.

eg : First stream is related to “Date of Birth” milestone type, so we have to hard code the “Birthday” values in the Milestone type field.so that we can identify these dates records are related to “Birthday” milestone types.

eg : After creating the Milestone type field, now it's time to add the milestone dates, so we have just called “Date of Birth” original field on the Milestone dates field.

let's drop the unnecessary fields from the stream.

eg : In the final output we only wanted the person Name, Milestone type, Milestone date fields. so, lets select all the unnecessary fields and select the drop function in the toolbar to drop the unnecessary fields.

Each field’s final stream would seem as follows.

Each field Stream final output

we have to repeat the above steps for all the remaining columns/fields which needs to transpose.

For the Wedding Anniversary stream Milestone Type Would be “Wedding Anniversary” and Milestone date would be “Wedding Anniversary Date". and drop the unnecessary fields.

Wedding Anniversary stream transform node output

likewise, for the Client anniversary stream Milestone Type Would be “Client Anniversary” and Milestone date would be “Client Anniversary Date ". and drop the unnecessary fields.

Client Anniversary stream transform node output

Following the construction of a different stream, the output will look like below

different stream output

We can now generate the final dataset by appending each stream up to the top with its corresponding field.

eg : in the below screenshot we are appending the Wedding anniversary client millstone type to the Birthdate Client anniversary streams.

append 1

eg : in the below screenshot we are appending the Client anniversary streams to the Wedding anniversary client millstone type & Birthdate Client anniversary streams.

append 2

Note: The transform node can be inserted after the final append node, and from there, we can generate as many more calculated fields as needed.

After the union/appends of all the different streams, we are good to register as a dataset by using the output node.

Register Dataset

Consideration:

The number of rows will increase to correspond with the number of columns we are attempting to transpose because this columns into rows transpose (unpivot) transforms the wider data into tall data.

Notes:

It’s not always necessary to register these transposed rows as a dataset; in certain use cases, you may want to move these transposed records into higher streams via left joins or multivalued joins, depending on the use case you’re trying to solve.

Output:

we have successfully transposed all the columns into rows using the above approach. the final output will be look likes below.

output dataset