Skip to main content
Question

Transposing Using the postcode and question number as a pair, possibly a list?

  • April 23, 2024
  • 2 replies
  • 111 views

sar_copp
Contributor
Forum|alt.badge.img+5

Morning, I am trying to transpose some data from a survey, I want to sort of Pair up the Postcode and the Question Number, then have columns of Responses with counts. Ive attached imagesΒ  and a fme workbenchΒ of the original data and the output I want. I have 36 questions, so want to know if there is a way to do this using a list? So I dont have to repeat this 36 times, I am not understanding how to do lists!
I want to output the postcode as a column, then have a question number column. So each postcode will be repeated depending on the number of questions e.g.

Question NoΒ  Β  Postcode

Question 1Β  Β  Β  M19 4NY

Question 2Β  Β  Β M19 4NY

Question 1Β  Β  Β  M4 7AT

Question 2Β  Β  Β M4 7AT

Β 

Then I want the next columns to be counts of the answers

Question NoΒ  Β  PostcodeΒ  Strongly AgreeΒ  Β  Β  AgreeΒ  Β  Β  NeitherΒ  Β  DisagreeΒ  Β  Β Strongly Disagree

Question 1Β  Β  Β  M19 4NYΒ  Β  Β  Β  Β  Β  Β 2Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β 3Β  Β  Β  Β  Β  Β  Β  Β 1Β  Β  Β  Β  Β  Β  Β  Β 0Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  2

Question 2Β  Β  Β M19 4NYΒ  Β  Β  Β  Β  Β  Β 5Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β Β  Β  7Β  Β  Β  Β  Β  Β  Β  Β 0Β  Β  Β  Β  Β  Β  Β  Β  3Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  0

Question 1Β  Β  Β  M4 7ATΒ  Β  Β  Β  Β  Β  Β 1Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  2Β  Β  Β  Β  Β  Β  Β  Β  1Β  Β  Β  Β  Β  Β  Β  Β 5Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β 6

Question 2Β  Β  Β M4 7ATΒ  Β  Β  Β  Β  Β  Β  Β 3Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β 4Β  Β  Β  Β  Β  Β  Β  Β Β  1Β  Β  Β  Β  Β  Β  Β  4Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β  Β 4

Β 

2 replies

jkr_wrk
Influencer
Forum|alt.badge.img+37
  • May 2, 2024

@sar_coppΒ Is this question still open?

I think your data looks pretty good for an excel pivot table to get the results you want.

But this will give the result in fme:

Β 


sar_copp
Contributor
Forum|alt.badge.img+5
  • Author
  • Contributor
  • May 2, 2024

@sar_coppΒ Is this question still open?

I think your data looks pretty good for an excel pivot table to get the results you want.

But this will give the result in fme:

Β 

I am indeed looking for the answer - my excel screen print was just an illustration of what I need, havent managed to achieve it. Will look at what yo’ve done