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
- Offline
- Official LimeSurvey Partner
Less
More
10 years 2 months ago #104011
by Mazi
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
Replied by Mazi on topic Retrieve surveys survey results in normalized form
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
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
- Topic Author
- Offline
- New Member
Less
More
- Posts: 15
- Thank you received: 1
10 years 2 months ago #104021
by lordzoster
Replied by lordzoster on topic Retrieve surveys survey results in normalized form
Actually I wanted to contribute in that way
Thank you for pointing it
Thank you for pointing it
The following user(s) said Thank You: itsme
The topic has been locked.
- itsme
- Offline
- Junior Member
Less
More
- Posts: 23
- Thank you received: 0
10 years 2 months ago #104022
by itsme
Replied by itsme on topic Retrieve surveys survey results in normalized form
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?
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
- Offline
- Official LimeSurvey Partner
10 years 2 months ago #104035
by Mazi
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
Replied by Mazi on topic Retrieve surveys survey results in normalized form
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.
- quaint
- Offline
- New Member
Less
More
- Posts: 10
- Thank you received: 1
10 years 2 months ago #104997
by quaint
Replied by quaint on topic Retrieve surveys survey results in normalized form
Hello Lordzoster, Mazi,
Getting an export of normalised survey data in the format specified by itsme (eg)
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!
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
- Topic Author
- Offline
- New Member
Less
More
- Posts: 15
- Thank you received: 1
10 years 2 months ago - 10 years 2 months ago #105000
by lordzoster
Replied by lordzoster on topic Retrieve surveys survey results in normalized form
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:
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:
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
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);
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 2 months ago by lordzoster.
The topic has been locked.
- lordzoster
- Topic Author
- Offline
- New Member
Less
More
- Posts: 15
- Thank you received: 1
10 years 2 months ago #105056
by lordzoster
Replied by lordzoster on topic Retrieve surveys survey results in normalized form
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.