Bienvenue, Invité
Nom d'utilisateur : Mot de passe : Se souvenir de moi

SUJET : tracking completion status of translations

tracking completion status of translations il y a 1 an 6 mois #92826

  • TMSWhite
  • Portrait de TMSWhite
  • Hors ligne
  • LimeSurvey Team
  • Messages : 759
  • Remerciements reçus 82
  • Karma: 36
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
L'administrateur a désactivé l'accès en écriture pour le public.
Cet utilisateur a été remercié pour son message par: Ben_V

tracking completion status of translations il y a 1 an 6 mois #92829

  • Ben_V
  • Portrait de Ben_V
  • Hors ligne
  • Platinum Lime
  • Messages : 1068
  • Remerciements reçus 235
  • Karma: 74
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 ?

capt_1_2013-02-20.gif


Thank you
Ben.
Benoît

goo.gl/Bw5iM => Recherche GG dans le forum français (remplacer "exemple" dans la barre de recherche)
goo.gl/WX8PH => GG search for english forum (Replace "example" in the search bar)
goo.gl/IxiGu => Búsqueda en el foro en español (Cambiar "ejemplo" en la barra de...
Dernière édition: il y a 1 an 6 mois par Ben_V.
L'administrateur a désactivé l'accès en écriture pour le public.

tracking completion status of translations il y a 1 an 6 mois #92842

  • TMSWhite
  • Portrait de TMSWhite
  • Hors ligne
  • LimeSurvey Team
  • Messages : 759
  • Remerciements reçus 82
  • Karma: 36
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.
L'administrateur a désactivé l'accès en écriture pour le public.

tracking completion status of translations il y a 1 an 6 mois #92844

  • Ben_V
  • Portrait de Ben_V
  • Hors ligne
  • Platinum Lime
  • Messages : 1068
  • Remerciements reçus 235
  • Karma: 74
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

goo.gl/Bw5iM => Recherche GG dans le forum français (remplacer "exemple" dans la barre de recherche)
goo.gl/WX8PH => GG search for english forum (Replace "example" in the search bar)
goo.gl/IxiGu => Búsqueda en el foro en español (Cambiar "ejemplo" en la barra de...
L'administrateur a désactivé l'accès en écriture pour le public.

tracking completion status of translations il y a 1 an 6 mois #92857

  • Ben_V
  • Portrait de Ben_V
  • Hors ligne
  • Platinum Lime
  • Messages : 1068
  • Remerciements reçus 235
  • Karma: 74
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

goo.gl/Bw5iM => Recherche GG dans le forum français (remplacer "exemple" dans la barre de recherche)
goo.gl/WX8PH => GG search for english forum (Replace "example" in the search bar)
goo.gl/IxiGu => Búsqueda en el foro en español (Cambiar "ejemplo" en la barra de...
Dernière édition: il y a 1 an 6 mois par Ben_V.
L'administrateur a désactivé l'accès en écriture pour le public.
Modérateurs: ITEd
Temps de génération de la page : 0.194 secondes
Donation Image