Welcome, Guest
Username: Password: Remember me

TOPIC: tracking completion status of translations

tracking completion status of translations 2 years 11 months ago #92826

  • TMSWhite
  • TMSWhite's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 83
  • Karma: 37
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.
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 administrator has disabled public write access.
The following user(s) said Thank You: Ben_V

tracking completion status of translations 2 years 11 months ago #92829

  • Ben_V
  • Ben_V's Avatar
  • Offline
  • Platinum Lime
  • Posts: 1588
  • Thank you received: 390
  • Karma: 102
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
Last Edit: 2 years 11 months ago by Ben_V.
The administrator has disabled public write access.

tracking completion status of translations 2 years 11 months ago #92842

  • TMSWhite
  • TMSWhite's Avatar
  • Offline
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 83
  • Karma: 37
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 administrator has disabled public write access.

tracking completion status of translations 2 years 11 months ago #92844

  • Ben_V
  • Ben_V's Avatar
  • Offline
  • Platinum Lime
  • Posts: 1588
  • Thank you received: 390
  • Karma: 102
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
The administrator has disabled public write access.

tracking completion status of translations 2 years 11 months ago #92857

  • Ben_V
  • Ben_V's Avatar
  • Offline
  • Platinum Lime
  • Posts: 1588
  • Thank you received: 390
  • Karma: 102
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
Last Edit: 2 years 11 months ago by Ben_V.
The administrator has disabled public write access.
Moderators: ITEd
Time to create page: 0.162 seconds