-
Re: Multiple survey analysis problem
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
question answer respondent_id ClientA a alfa 1 x b beta 1 x named: 128081116
question answer respondent_id ClientB a gamma 2 y b delta 2 y named: test
question answer respondent_id ClientC a alfa 3 z b beta 3 z 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.
-
test.xlsx 7.9 K
-
128084936.xlsx 7.9 K
-
128081116.xlsx 7.8 K
-
Re: Multiple survey analysis problem
Rachel Zhong Jan 29, 2018 9:04 PM (in response to Simone Trabattoni)Hi Simone, thanks for your suggestion. Currently the SurveyMonkey connection is not good, I will try it later.
-