I have a survey asking participants to rank their favourite colours—Red, Blue, and Green—in numerical order (1, 2, 3).
I want to use the results to create a workflow that counts how many times each colour has been picked based on the ranking.
Here is some example data.
Sample Data
Unique_ID
Colour
Rank
1
Red
3
1
Blue
2
1
Green
1
2
Red
1
2
Blue
2
2
Green
3
3
Red
1
3
Blue
2
3
Green
3
What I am trying to achieve
Colour
Rank_ 1_Count
Rank_2_Count
Rank _3_Count
Red
2
0
1
Blue
0
3
0
Green
1
0
2
Page 1 / 1
Have you tried using the AttributePivoter?
The AttributePivoter receives features with attributes, restructures and regroups them based on specified Group By attributes, and calculates summary statistics based on a designated Attribute To Analyze in order to form a Pivot table output.
Thanks for getting back. Unfortunately it’s not giving me the table structure I wanted.
I am chasing something like this
Did some further research.
Dmitri suggestion seems useful but I can’t see his attachment
Looking at this one, they place an AttributeExposer after the AttributePivoter > Data port
something like this
Thanks for getting back again. I am not getting any luck with the outputs after entering 1,2,3. Are you able to provide your workbench file?
Also, is there any chance to further proceed the data structure by having the colours grouped like the following
Much appreciated!!!
Hi @calvin.fme ,
The column calculation as per your initial message can be achieved with the AttributePivoter as suggested by @j.botterill. I think what you experienced is a bug in AttributePivoter you can see in some versions like FME 2023. In those affected versions, AttributePivoter adds .0 suffix to attribute names when they have integers. Therefore, those attribute names created by AttributePivoter looks like 1.0, 2.0, 3.0 instead of 1,2,3. This can easily be rectified with a BulkAttributeRenamer.
The attached workspace produce the the output as you mentioned in the last response. The workflow got little bit complex to handle Grand Total column and row.
Hope this helps!
I went another way, using ListKeyValuePairExtractor transformer from FME Hub.
