2 Replies Latest reply: Jan 29, 2018 9:04 PM by Rachel Zhong RSS

    Multiple survey analysis problem

    Rachel Zhong
    Rachel Zhong Jan 28, 2018 8:22 PM

      Hi dear all, I am new to Qlik and am ready to use qlik to analysis my survey results. Unfortunately I was stuck at combing multiple survey results to one table.

      I have connected Survey Monkey and ready to load survey A and survey B. Survey A and Survey B are the same questions but are distributed to different participants(Company A and Company B).

       

      Now in Qlik I want to combine these results together and name the table 'Final results', in this 'Final results' table the fields are

       

      Respondent_ID, Client, Question, Question_answer

       

      Respondent_ID, Question and Question_answer are loaded directly from SurveyMonkey, and 'Client' will be valued as 'Client A' and 'Client B'.

       

      and I am trying to use the script below but not work:

       

      [Mid_Program Participant template]:
      LOAD respondent_id as [GetResponses.respondent_id],
           'ClientA' as Client_Mid.Pro,
      question as [GetResponses.question],
      answer as [GetResponses.answer];

       

      SELECT respondent_id,
      question,
      answer
      FROM GetResponses
      WITH PROPERTIES (
      surveyId='128084936'
      );

       

      LOAD respondent_id as [GetResponses.respondent_id],
           'Client B' as Clinet_Mid.Pro,
      question as [GetResponses.question],
      answer as [GetResponses.answer];

      SELECT respondent_id,
      question,
      answer
      FROM GetResponses
      WITH PROPERTIES (
      surveyId='128081116'
      );

       

      Can anyone give some suggestions?  This process should be repeated, as there will be more tables are added in the future.

        • Re: Multiple survey analysis problem
          Simone Trabattoni
          Simone Trabattoni Jan 29, 2018 9:42 AM (in response to Rachel Zhong)

          Hi, I've tried to create some fake data, and put them in some excel.

           

          named; 128084936

          questionanswerrespondent_idClientA
          aalfa1x
          bbeta1x

           

          named: 128081116

          questionanswerrespondent_idClientB
          agamma2y
          bdelta2y

           

          named: test

          questionanswerrespondent_idClientC
          aalfa3z
          bbeta3z

           

           

          Then, I made this script that with some loop is going to add each new survey, modifying only a pair of variable:

           

          // first you have to load the first survey

          data1:

          LOAD

            *

          FROM [lib://store/128084936.xlsx]

          (ooxml, embedded labels, table is Foglio1);

           

           

          // second, you have to put here all the other survey name 'x','y', ...

          // you can manage this variable also externally to the load editor, using a must include.

          set vsurveyId ='128081116','test';

           

          // third, this loop is going to load all the survey with the name in the vsurveyId variable.

          // The problem is that dhe ClientA,ClientB etc. are different in the column name, so with a join

          // we are going to put them in different columns.

          FOR EACH file in $(vsurveyId)

          JOIN(data1)

          LOAD

            *

          FROM [lib://store/$(file).xlsx]

          (ooxml, embedded labels, table is Foglio1);

           

          next

           

          // fourth: another loop to merge the Clients in one column

          // we have to load the first one

          data2:

          load

              question as [GetResponses.question],

              answer as [GetResponses.answer],

              respondent_id as [GetResponses.respondent_id],

              ClientA as [Client_Mid.Pro]

          resident data1

          where len(ClientA)>0;

           

          // fifth: here the clients, as the surveys, you ought to add the new ones.

          set client ='ClientB','ClientC';

           

          // sixth: here the loop to do for each client whom is going to be concatenated

          // to the first read

          FOR EACH cliente in $(client)

           

          concatenate(data2)

          load

              question as [GetResponses.question],

              answer as [GetResponses.answer],

              respondent_id as [GetResponses.respondent_id],

              $(cliente) as [Client_Mid.Pro]

          resident data1

          where len($(cliente))>0;

          next

           

          // seventh, whe drop the data1 table that is useless, and uses memory.

          drop table data1;

           

           

          Hope it is going to help! Attached the xlsx I'm using in this example.