Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have data like below
Month | A: Pass | A: Fail | B:Pass | B:Fail |
Jan | 10 | 10 | 20 | 30 |
Feb | 20 | 20 | 20 | 20 |
What is the best way to categorize the above data and create a pivot table out of it. I am looking for the below pivot table
Need Group A and B at top (horizontally)
Group | A | B | ||
Month | A: Pass | A: Fail | B:Pass | B:Fail |
Jan | 10 | 10 | 20 | 30 |
Feb | 20 | 20 | 20 | 20 |
Basically you need to pivot your data and create another field with the values 'A' and 'B' that you can use as a dimension in your pivot table. First we pivot the table using the crosstable function:
Temp_table:
Crosstable(Category, Value, 1)
Load
Month,
A: Pass,
A: Fail,
B:Pass,
B:Fail
<from whatever your source is>
Then we can derive the new column:
Final_table:
Load
Month,
Category,
if(FindOneOf(Category, 'A:') > 0, 'A',
if(FindOneOf(Category, 'B:') > 0, 'B')) as Group,
Value;
resident Temp_table;
drop table Temp_table; //as it is no longer needed
Now you can build your pivot table with Month, Group and Category as dimensions and value as your measure. Aligning them the way you want is just a matter of moving them around.
I hope that puts you on the right track.
Basically you need to pivot your data and create another field with the values 'A' and 'B' that you can use as a dimension in your pivot table. First we pivot the table using the crosstable function:
Temp_table:
Crosstable(Category, Value, 1)
Load
Month,
A: Pass,
A: Fail,
B:Pass,
B:Fail
<from whatever your source is>
Then we can derive the new column:
Final_table:
Load
Month,
Category,
if(FindOneOf(Category, 'A:') > 0, 'A',
if(FindOneOf(Category, 'B:') > 0, 'B')) as Group,
Value;
resident Temp_table;
drop table Temp_table; //as it is no longer needed
Now you can build your pivot table with Month, Group and Category as dimensions and value as your measure. Aligning them the way you want is just a matter of moving them around.
I hope that puts you on the right track.
Did Rodj's post get you what you needed to get your layout as you wished? If so, do not forget to return to this thread and use the Accept as Solution button on that post to give them credit for the assistance. This also confirms to other Community Members that things worked too. If you are still working upon things, please leave an update. Since you do have the other post as well, you may also want to provide the link to each of them in the other post too.
Regards,
Brett
Apparently you came up with the solution Brett, well done! 😉
@sayadutt I don't know if you can fix it, but it would be nice if you did.