Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

xiyuzw1113
New Contributor III

Multiple survey analysis problem

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.

2 Replies
simotrab
Contributor II

Re: Multiple survey analysis problem

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.

xiyuzw1113
New Contributor III

Re: Multiple survey analysis problem

Hi Simone, thanks for your suggestion. Currently the SurveyMonkey connection is not good, I will try it later.