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.
