Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: Retrieve surveys survey results in normalized form

Retrieve surveys survey results in normalized form 6 months 3 days ago #103990

  • itsme
  • itsme's Avatar
  • OFFLINE
  • Senior Lime
  • Posts: 53
  • Thank you received: 1
  • Karma: 0
sorry, too complicated for my level, i appreciate your efforts and your time, it seems needs to try, but without further instructions where to put that and how to execute on my localhost i wouldn't be able to apply :(
lets see the guys when apply, perhaps they can tell us where to put that and how step by step :)
thanks a lot again

Mike
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 6 months 2 days ago #103991

  • lordzoster
  • lordzoster's Avatar
  • OFFLINE
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
In a few hours I'll have the OK from my client and thus in next days I rewrite the whole code in a more usable way, maybe an addon for LS.
In case, I'll post here.
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 6 months 2 days ago #104011

  • Mazi
  • Mazi's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 5300
  • Thank you received: 291
  • Karma: 247
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
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 6 months 2 days ago #104021

  • lordzoster
  • lordzoster's Avatar
  • OFFLINE
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
Actually I wanted to contribute in that way :)
Thank you for pointing it
The administrator has disabled public write access.
The following user(s) said Thank You: itsme

Retrieve surveys survey results in normalized form 6 months 2 days ago #104022

  • itsme
  • itsme's Avatar
  • OFFLINE
  • Senior Lime
  • Posts: 53
  • Thank you received: 1
  • Karma: 0
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 administrator has disabled public write access.

Retrieve surveys survey results in normalized form 6 months 2 days ago #104035

  • Mazi
  • Mazi's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 5300
  • Thank you received: 291
  • Karma: 247
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
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 5 months 1 week ago #104997

  • quaint
  • quaint's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 14
  • Thank you received: 1
  • Karma: 0
Hello Lordzoster, Mazi,

Getting an export of normalised survey data in the format specified by itsme (eg)
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 administrator has disabled public write access.

Retrieve surveys survey results in normalized form 5 months 1 week ago #105000

  • lordzoster
  • lordzoster's Avatar
  • OFFLINE
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
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:
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:
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: 5 months 1 week ago by lordzoster.
The administrator has disabled public write access.

Retrieve surveys survey results in normalized form 5 months 1 week ago #105056

  • lordzoster
  • lordzoster's Avatar
  • OFFLINE
  • Junior Lime
  • Posts: 29
  • Thank you received: 2
  • Karma: 0
I update the SELECT, there could be an issue if the survey includes free text answers.
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 administrator has disabled public write access.
  • Page:
  • 1
  • 2
Moderators: ITEd
Time to create page: 0.358 seconds
Donation Image