1 Reply Latest reply: Feb 15, 2012 8:43 AM by Luis Durán RSS

    Questionnaire

      Hi all,

       

      Hopefully soemone can help me with this puzzle.

      I want to load a questionnaire and I have the following input file.

       

       

      CountryCompanyQ1Q2Q3Q4Q5Q6
      beabc123444
      frabc234522
      gbcde765677
      uscde578908

       

      Also I have a referential file

       

      Country

      Question CodeDescription
      Q1How are you
      Q2What is your rating of...?
      Q3What's your age?

       

      Now I would like to create the following structure

      UKEYQuestion CodeAnswer
      1Q11
      1Q22
      1Q33

       

      Where I can link the UKEY to a table with company/country info.

      How can I do this easily. Note the number of questions is like a 1000.

       

      For some reason the transpose function doesn't work as I want it to work.

       

      Best regards,

      Arjan

        • Re: Questionnaire

          Hi Arjan,

          I´ve Created a source file with your data (Quest.xls) and used it to obtain  the desired table. You can do it with CrossTable (it has an assistant to do it. It´s a Transformation Step)


          Please se below the script:


          [Referential File]:

          LOAD [Question Code],

               Description

          FROM

          Quest.xlsx

          (ooxml, embedded labels, table is [Referential file]);



          Quest:

          CrossTable([Question Code], Answer, 2)

          LOAD Country,

               Company,

               Q1,

               Q2,

               Q3,

               Q4,

               Q5,

               Q6

          FROM

          Quest.xlsx

          (ooxml, embedded labels, table is Quest);



          -- If you have Endless questions, you can use * instead of field names. Hope this helps.


          Regards

          L.