How to do pivot Column to rows
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?
Tagged:
1
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?
Answers
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)
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;
@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.
@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
@Mahulima i've found a way, but this assumes the number of columns and rules you have is fixed.
If you need something more than this, I suggest either
@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.
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.
@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
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.
@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.
Source
Output