How to do pivot Column to rows

Mahulima

I have rule1, rule2, rule3 in the column names and corresponding count in the rows, now I want to get the result as Column Names : Rule, count

How I can achieve this?

Josh Boxer

Currently there is not a Pivot step natively in the product, but I am aware of a Custom Step that has been created to achieve this. I have added another vote to the backlog request to add this functionality.

However, you might not need to pivot as the Validate step has outputs that contain the Rule name in a column so you could filter the metric name column for say Failed rows to get the count for every rule.

(Note there was a change to these outputs in 2.2.5 if you are using an older version)

Mahulima

This is what I have , I have come to the counts by multiple group step and then collecting the stats from different channels by joins

And I want to convert this to like below;

Sueann See

@Mahulima Is there any reason why you couldn't incorporate those Rules into a Validate step?

The Validate step gives you a Results by Rule

This already gives you something like this:

To get the Batch Date, you can just take snapshot, add batch timestamp.

To remove the columns you don't need, you just need to use a Transform step to hide the additional columns.

Mahulima

@Sueann See My requirement is to get the number if companies which has atleast one contact with email and mobile, One company has multiple contact, I used Validate step to get the contacts which has email and mobile, then used filter on the rules based on the rule flag Pass, after that Used group to get the count of the unique companies, similarly I had other rules and similar way I used filter for that rule and again group to get the company count for that rule. I had six different rules and I got the company count for 6 different group result and then at the end I used joins to get all the company count in one report, Now I want Pivot by rules so that I can show in One Trend Chart Otherwise I have to create 6 different trend chart for 6 different rules

Sueann See

@Mahulima i've found a way, but this assumes the number of columns and rules you have is fixed.

• Use a Transform step to keep only the columns related to Email and Mobile and hide the rest of the columns. Create a new column to store the Rule Name as a constant value.

• Use another Transform step to keep only the columns related to Email or Mobile. Create a new column to store the Rule Name as a constant value.

• Union the transformed results. You will get something like this:

If you need something more than this, I suggest either

• look at whether you can do the grouping before the Validate step, or
• contact your account manager to explore the possibility of having the Pivot custom step.

Mahulima

@Sueann See Thank You very much! Grouping before validate will not work in my case and also Union will not work because I need to show as report in one row as well. And I have 10 rules so in that case If I need to add union additionally they I might need multiple unions just to achieve the trend in one chart which will make the workflow heavy and complex. But thank you so much for your help. For the time being I am doing in different charts and showing in one single report.

Sami Laine

Pivot seems to be quite common requirement. Business users are used to Excel pivoting and its useful way to dynamically create two dimensional comparisons. Especially during development and ad hoc cases its a productivity problem if you have to move data to different tool like PowerBI and use its pivot while exploring data and building workflows in Aperture. Its also security and privacy issue. Moving all data that is needed for analysis from tool to another might not be acceptable. Only protected data conforming to local data policies can be moved to PowerBI.

Sueann See

@Mahulima i was looking at this again and realize there is a way you can create a single trend chart without pivoting your data.

The way to do it is to add multiple Y-axis - one for every Rule.

Of course, the disadvantages to this method is that

• you will not be able to export the data in the format similar to the Validate Rule Results.
• you will have to configure as many Y-axis as the number of rules you have - it takes a little more time, and it is not dynamic i.e. you will have to remember to add new rules as new Y-axis series.

By the way, could you send me a dmx (export) for your workflow? i'm still interested to check if there is way for you to utilize the Validate step.

Sueann See

@Mahulima we have released the Columns to rows step since release 2.6.10. For your case, since you are not converting all the values into a single column, and there are actually 2 sets of values i.e. Count and Pass Rate that you need to convert, you will not be able to achieve this in a single step, but we hope we have at least made it a little easier for now. You can refer to the example below on how to accomplish this.

• Use Columns to rows step once for every set of value you want to convert.
• Use Splice step to combine the results so they appear side by side
• Use Transform step to hide the redundant columns, then rename the Rule Name column and applying a function to derive the Rule Name.

Source

Output