Welcome to the LimeSurvey Community Forum

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

tracking completion status of translations

  • TMSWhite
  • TMSWhite's Avatar Topic Author
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
11 years 4 weeks ago #92826 by TMSWhite
I have several translators around the world working to translate a handful of instruments into a dozen languages. Limesurvey doesn't natively provide a dashboard showing how complete translations are. However, I've found the following query helpful. It has been tested for MySql and assumes a base language of English. Hoping this might help others too.
Code:
select qs_stat.surveyls_title, qs_stat.language, qs_stat.num_qs, qs_stat.percent_trans as pct_q_trans, as_stat.percent_trans as pct_a_trans
from
(select l.surveyls_title , a.language, a.num_qs, a.percent_trans
from
(select b.sid, b.language, count(b.qid) as num_qs, (sum(b.translated) / count(b.translated)) * 100 as percent_trans
from
(select q.sid, q.qid, q.question, q.language, 
  (case when q.question != e.question then 1 else 0 end) as translated
from limesurvey_questions as q, limesurvey_questions as e
where q.type != '*' and q.language != 'en' and
  e.sid = q.sid and e.qid = q.qid and e.language = 'en') as b
group by b.sid, b.language) as a,
limesurvey_surveys_languagesettings l
where a.sid = l.surveyls_survey_id and l.surveyls_language = 'en'
order by a.sid, a.percent_trans desc, a.language) as qs_stat,
(select l.surveyls_title, qstat.sid, qstat.language, qstat.num_qs, qstat.percent_trans
from
(select ansstat.sid, ansstat.language, count(distinct ansstat.qid) as num_qs, (sum(ansstat.pct_trans) / count(ansstat.pct_trans)) * 100 as percent_trans
from
(
select q.sid, q.qid, q.language, (sum(trans.translated)/ count(trans.translated)) as pct_trans
from
(select ans.qid, ans.answer, ans.language, 
  (case when ans.answer != en.answer then 1 else 0 end) as translated
from limesurvey_answers as ans, limesurvey_answers as en
where ans.language != 'en' and en.language = 'en' and
  ans.qid = en.qid and ans.scale_id = en.scale_id and ans.code = en.code) as trans,
limesurvey_questions as q
where q.qid = trans.qid and q.language = trans.language
group by q.qid, q.language
) as ansstat
group by ansstat.sid, ansstat.language) 
as qstat,
limesurvey_surveys_languagesettings l
where qstat.sid = l.surveyls_survey_id and l.surveyls_language = 'en'
order by qstat.sid, qstat.percent_trans desc, qstat.language) as as_stat
where qs_stat.surveyls_title = as_stat.surveyls_title and qs_stat.language = as_stat.language
order by qs_stat.surveyls_title, qs_stat.percent_trans desc, qs_stat.language
The following user(s) said Thank You: Ben_V
The topic has been locked.
More
11 years 3 weeks ago - 11 years 3 weeks ago #92829 by Ben_V
Replied by Ben_V on topic tracking completion status of translations
Hello Tom,

I've tried your terribly efficient and obviously useful new invention!
Just a question... (because PhpMyAdmin displays some buttons I've never seen before)

What 'pct_a_trans' exactly means ?



Thank you
Ben.

Benoît

EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
Last edit: 11 years 3 weeks ago by Ben_V.
The topic has been locked.
  • TMSWhite
  • TMSWhite's Avatar Topic Author
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
11 years 3 weeks ago #92842 by TMSWhite
Replied by TMSWhite on topic tracking completion status of translations
Pct_a_trans = percent of answers translated. Pct_q_trans = percent of questions or subquestions translated.
This isn't perfect because some phrases are identical across languages, but it is a good start.
The topic has been locked.
More
11 years 3 weeks ago #92844 by Ben_V
Replied by Ben_V on topic tracking completion status of translations
Yes a very very good start... already valuable to keep an eye on translation progress, particularly for neverending questionnaires!
That's very kind of you to share this.

Benoît

EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
The topic has been locked.
More
11 years 3 weeks ago - 11 years 3 weeks ago #92857 by Ben_V
Replied by Ben_V on topic tracking completion status of translations

because some phrases are identical across languages

For the less, accepting "no" as a valid english/spanish translation can provide a significant change

Benoît

EM Variables => bit.ly/1TKQyNu | EM Roadmap => bit.ly/1UTrOB4
Last Releases => 2.6x.x goo.gl/ztWfIV | 2.06/2.6.x => bit.ly/1Qv44A1
Demo Surveys => goo.gl/HuR6Xe (already included in /docs/demosurveys)
Last edit: 11 years 3 weeks ago by Ben_V.
The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose