Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Retrieve surveys survey results in normalized form

  • Mazi
  • Mazi's Avatar
  • Offline
  • Official LimeSurvey Partner
  • Official LimeSurvey Partner
More
10 years 1 month ago #104011 by Mazi
lordzoster, Limesurvey 2.05 offers a new plugin system which can be used to code custom export scripts and easily share such scripts with others.

Please have a look, maybe you can code one of the first Limesurvey export plugins.

Links:
manual.limesurvey.org/Plugins
www.limesurvey.org/en/extensions?sid=62:Export

Best regards/Beste Grüße,
Dr. Marcel Minke
Need Help? We offer professional Limesurvey support: survey-consulting.com
Contact: marcel.minke(at)survey-consulting.com
The topic has been locked.
  • lordzoster
  • lordzoster's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 1 month ago #104021 by lordzoster
Actually I wanted to contribute in that way :)
Thank you for pointing it
The following user(s) said Thank You: itsme
The topic has been locked.
More
10 years 1 month ago #104022 by itsme
Mazi, couldn't upgrade to 2.05 at all, it requires php 5.3 and other thing that i can change in the server not to affect other applications, so kept with 2.0 and installation went smooth
any idea for 2.0 limitation or things that i will stop with? i mean does it have preventive issues that will force me later to change the server or upgrade the php version so i can install the newer 2.05?
The topic has been locked.
  • Mazi
  • Mazi's Avatar
  • Offline
  • Official LimeSurvey Partner
  • Official LimeSurvey Partner
More
10 years 1 month ago #104035 by Mazi
Since there are no more changes at the Limesurvey 2.0 core, you should not have any problems later with that version.

Best regards/Beste Grüße,
Dr. Marcel Minke
Need Help? We offer professional Limesurvey support: survey-consulting.com
Contact: marcel.minke(at)survey-consulting.com
The topic has been locked.
More
10 years 1 month ago #104997 by quaint
Hello Lordzoster, Mazi,

Getting an export of normalised survey data in the format specified by itsme (eg)
Code:
id, attribute_1, attribute_2, question, answer
1, "Employer 1", "Site 1", "What do you think about X?", "It's great"
1, "Employer 1", "Site 1", "What do you think about Y?", "It's rubbish"
1, "Employer 1", "Site 1", "What do you think about Z?", "It's OK"
2, "Employer 1", "Site 2", "What do you think about X?", "It's OK"
2, "Employer 1", "Site 2", "What do you think about Y?", "It's great!"
2, "Employer 1", "Site 2", "What do you think about Z?", "It's rubbish!"

would be very, very useful for my organisation.

I haven't yet updated to 2.05; the next clear window of opportunity to do so (and rebuild the server!) will be towards the late end of March, but a plugin would definitely be installed and regularly used!
The topic has been locked.
  • lordzoster
  • lordzoster's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 1 month ago - 10 years 1 month ago #105000 by lordzoster
Hi all
I'm not disappeared :)
Yesterday I finally managed to compact all the logic within a single SQL (I'm a db guy after all) getting rid of the PHP part except for the JSON extraction (too sad MySQL doesn't offer an out-of-the-box solution for returning JSON directly yet).
Moreover I wrote a little bit cleaner and reusable code, without the hardcoded character positions (the "X" of the SGQA format in the column names).

Here it is an early release of the SQL:
Code:
DROP PROCEDURE IF EXISTS spLime_NormalizeIt;
 
DELIMITER //
 
CREATE PROCEDURE spLime_NormalizeIt (survey_code INT)
BEGIN
/*
  Author: Raffaele Turra - SyntegraDMC
  Created: 2014-02-08
 
  Create an UNPIVOT from survey results
  considering only the columns containing the questions
  Note: LS creates acolumn for each question, in the sidXgidXqid format
  and values are answers' codes
*/
 
SET group_concat_max_len=15000;
SET @r_query = NULL;
# SET survey_code = 869765;
SET @drop_stmt = CONCAT("DROP TABLE IF EXISTS lime_survey_", survey_code, "_normalized;");
SET @create_stmt = CONCAT("CREATE TABLE lime_survey_", survey_code, "_normalized LIKE lime_survey__normalized;");
 
SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    "SELECT  LS.token
    , LS.datestamp
    , \"", RIGHT(g.group_name, 5), "\" AS codice_reato
    , \"", q.Title, "\"  AS codice_domanda
    , LA.qid
    , LS.`", c.column_name, "`  AS answer_code
    , LA.assessment_value
  from lime_answers AS LA
  INNER JOIN `lime_survey_", survey_code , "` AS LS
    ON LA.code = LS.`", c.column_name, "`
    AND LA.qid = substring_index(\"", c.column_name, "\", 'X', -1)"
    ) SEPARATOR " UNION ALL "
) INTO @r_query
from information_schema.columns c
left join lime_groups AS g
  ON g.gid = substring_index(substring_index(column_name, 'X',2) , 'X', -1)
left join lime_questions AS q
  ON g.gid = q.gid
  and q.qid = substring_index(column_name, 'X', -1)
where substring_index(column_name, 'X', 1) = substring_index(table_name, '_', -1)
  AND substring_index(column_name, 'X', 1) = CAST(survey_code AS char(10))
ORDER BY q.question_order;
 
SET @r_query = CONCAT("INSERT INTO lime_survey_"
  , survey_code
  , "_normalized 
  "
  , @r_query);
 
PREPARE dropstmt FROM @drop_stmt;
EXECUTE dropstmt;
DEALLOCATE PREPARE dropstmt;
 
PREPARE createstmt FROM @create_stmt;
EXECUTE createstmt;
DEALLOCATE PREPARE createstmt;
 
PREPARE selstmt FROM @r_query;
EXECUTE selstmt;
DEALLOCATE PREPARE selstmt;
END //
 
DELIMITER ;
 
CALL spLime_NormalizeIt (869765);
This code basically creates a stored procedure which accepts the survey sid as an input, and creates a "normalized" table using an existing one as a template, here the CREATE statement:
Code:
DROP TABLE IF EXISTS lime_survey__normalized;
 
CREATE TABLE lime_survey__normalized (
  token varchar(50)
  , `datestamp` datetime NOT NULL
  , codice_reato varchar(50)
  , codice_domanda varchar(50)
  , qid INT
  , answer_code  varchar(5)
  , assessment_value int
);

Note: "codice_domanda" is italian for "question_code" and it refers to the "question title" as named in LS;
"codice_reato" is specific for my LS application, and corresponds to the code entered for the question group.
HTH
Last edit: 10 years 1 month ago by lordzoster.
The topic has been locked.
  • lordzoster
  • lordzoster's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 1 month ago #105056 by lordzoster
I update the SELECT, there could be an issue if the survey includes free text answers.
Code:
SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    "SELECT  LS.token
    , LS.submitdate
    , \"", RIGHT(g.group_name, 5), "\" AS codice_reato
    , \"", q.Title, "\"  AS codice_domanda
    , LA.qid
    , LS.`", c.column_name, "`  AS answer_code
    , LA.assessment_value
  FROM `lime_survey_", survey_code , "` AS LS
  LEFT JOIN lime_answers AS LA
    ON LA.code = LS.`", c.column_name, "`
    AND LA.qid = substring_index(\"", c.column_name, "\", 'X', -1)"
    ) SEPARATOR " UNION ALL "
) INTO @r_query
from information_schema.columns c
left join lime_groups AS g
  ON g.gid = substring_index(substring_index(column_name, 'X',2) , 'X', -1)
left join lime_questions AS q
  ON g.gid = q.gid
  and q.qid = substring_index(column_name, 'X', -1)
where substring_index(column_name, 'X', 1) = substring_index(table_name, '_', -1)
  AND substring_index(column_name, 'X', 1) = CAST(survey_code AS char(10))
ORDER BY q.question_order;
The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose