## LaTeX forum ⇒ General ⇒ Datatool : search key, add up values, copy & past in a list Topic is solved

LaTeX specific issues not fitting into one of the other forums of this category.
pasbal
Posts: 12
Joined: Thu Jun 26, 2014 10:00 pm

### Datatool : search key, add up values, copy & past in a list

Hello,

I have many difficulties to use datatool package because i am new with latex.
I have a CSV file made up something like this:

\begin{filecontents}{dbTest.csv}
ColA,ColB,ColC
A,1,A
B,1.5,Ba
A,1.25,DSa
D,2.5,Aza
B,0.5,RE
\end{filecontents}

• How can i reuse \DTLrowcount{dbTest} with an mathematical operation ?
Expected result : Rows = 5
• I would like to count each key equal to A then B ... in the column A and use it for a calcul. I found this code

\def\Somme{0}
\Somme

but is there a similar command like \DTLsumcolumn{dbTest}{ColB}{\Total}\Total ?

Expected result
A=2 => 2/5
B=2 => 2/5
D=1 => 1/5
• I would like to add each value in the column B when the key is equal A then B ... and use it for a calcul.

Expected result
A 2.25/6.75 =
B 2/6.75
D 2.5/6.75

• I would like to list each string in the column C when the key is equal A then B ... and use it with \cref

Expected result
List A => \cref{A,DSa}
List B => \cref{Ba,RE}

I enclose my file.
Thanks and regards
Attachments
Test-DataTool.tex

rais
Posts: 289
Joined: Sun Nov 16, 2014 8:51 pm
Hi,
pasbal wrote:
• How can i reuse \DTLrowcount{dbTest} with an mathematical operation ?
Expected result : Rows = 5

Isn't that what \DTLrowcount{dbTest} yields in this case?
Or are you after something like \edef\numberofrows{\DTLrowcount{dbTest}}, i.e., to be able to use \numberofrows as a variable?
pasbal wrote:
• I would like to count each key equal to A then B ... in the column A and use it for a calcul. I found this code

\def\Somme{0}
\Somme

but is there a similar command like \DTLsumcolumn{dbTest}{ColB}{\Total}\Total ?

Expected result
A=2 => 2/5
B=2 => 2/5
D=1 => 1/5
• I would like to add each value in the column B when the key is equal A then B ... and use it for a calcul.

Expected result
A 2.25/6.75 =
B 2/6.75
D 2.5/6.75

Well, instead of using \DTLforeach to create just the number of rows, you could use it to create the sum of relevant points.
The number of effective rows (since you're using a condition) can be obtained by calling \DTLsavelastrowcount.
pasbal wrote:
• I would like to list each string in the column C when the key is equal A then B ... and use it with \cref

Expected result
List A => \cref{A,DSa}
List B => \cref{Ba,RE}

uhhmm, do you really have a label called A' in your TeX file (and another one called DSa')? Or do you have a label called A,DSa'? The latter does not work with cleveref's \cref command that way: the comma is used here as separator between labels.

\documentclass[12pt,a4paper]{article}
\usepackage[utf8]{inputenc}
\usepackage[francais]{babel}
\usepackage[T1]{fontenc}
\usepackage{datatool}
\usepackage[french]{cleveref}
\usepackage{filecontents}
\usepackage{siunitx}

\begin{document}

%création base
\begin{filecontents}{dbTest.csv}
ColA,ColB,ColC
A,1,A
B,1.5,Ba
A,1.25,DSa
D,2.5,Aza
B,0.5,RE
\end{filecontents}

\DTLdisplaydb{dbTest}
\edef\numberofrows{\DTLrowcount{dbTest}}%
\DTLsumcolumn{dbTest}{ColB}{\Total}
Total points : \Total

\def\SumB{0}
\DTLsavelastrowcount{\Somme}%
NB colonne A :\Somme

Number of rows: \numberofrows

\DTLdiv{\relrows}{\Somme}{\numberofrows}%
\DTLmul{\relrows}{\relrows}{100}%
\DTLround{\relrows}{\relrows}{1}%
A' rows: \SI{\relrows}{\percent} (\Somme/\numberofrows)

\DTLdiv{\SBa}{\SumB}{\Total}%
\DTLmul{\SBap}{\SBa}{100}%
\DTLround{\SBa}{\SBa}{2}%
\DTLround{\SBap}{\SBap}{1}%
\DTLround{\SumB}{\SumB}{2}%
\DTLround{\TotalR}{\Total}{2}%
A' rows contain \SumB\ points of total \TotalR\ points or \SBa, read \SI{\SBap}{\percent}
\end{document}


KR
Rainer

pasbal
Posts: 12
Joined: Thu Jun 26, 2014 10:00 pm
Hello,

Thanks for help. I will take some time to read, to assimilate your file, so that i'll write a new file much closer my idea.

pasbal.

pasbal
Posts: 12
Joined: Thu Jun 26, 2014 10:00 pm
Hello,

Your post was very helpful. Here a test that seems to work and totally compatible with my competences.

Thanks again and regards

    \documentclass[12pt,a4paper]{article}
\usepackage[utf8]{inputenc}
\usepackage[francais]{babel}
\usepackage[T1]{fontenc}
\usepackage{datatool}
\usepackage[french]{cleveref}
\usepackage{filecontents}
\usepackage{siunitx}
\usepackage{xstring}

\begin{document}

\DTLnewdb{dbScore}

$$y=a\times x +3 \label{EqA}$$
\DTLnewrow{dbScore}
\DTLnewdbentry{dbScore}{Competence}{Analyse}
\DTLnewdbentry{dbScore}{Name}{EqA}
\DTLnewdbentry{dbScore}{Score}{1}
\DTLnewrow{dbScore}
\DTLnewdbentry{dbScore}{Competence}{Redaction}
\DTLnewdbentry{dbScore}{Name}{EqA}
\DTLnewdbentry{dbScore}{Score}{1}

$$y=x \times x +3 \label{EqB}$$
\DTLnewrow{dbScore}
\DTLnewdbentry{dbScore}{Competence}{Analyse}
\DTLnewdbentry{dbScore}{Name}{EqB}
\DTLnewdbentry{dbScore}{Score}{2}
\DTLnewrow{dbScore}
\DTLnewdbentry{dbScore}{Competence}{Reason}
\DTLnewdbentry{dbScore}{Name}{EqB}
\DTLnewdbentry{dbScore}{Score}{2}

$$y=x +3 \label{EqC}$$
\DTLnewrow{dbScore}
\DTLnewdbentry{dbScore}{Competence}{Analyse}
\DTLnewdbentry{dbScore}{Name}{EqC}
\DTLnewdbentry{dbScore}{Score}{1.5}
\DTLnewrow{dbScore}
\DTLnewdbentry{dbScore}{Competence}{Redaction}
\DTLnewdbentry{dbScore}{Name}{EqC}
\DTLnewdbentry{dbScore}{Score}{1.5}

%%
\def\ListAnalyse{}
\DTLforeach[\DTLiseq{\Competence}{Analyse}]{dbScore}{\Competence=Competence,\Name=Name}{\xdef\ListAnalyse{\ListAnalyse,\Name}}
\StrGobbleLeft{\ListAnalyse}{1}[\ListAnalyse]

\def\SumAnalyser{0}
%\SumAnalyser

%%
\def\ListRedaction{}
\DTLforeach[\DTLiseq{\Competence}{Redaction}]{dbScore}{\Competence=Competence,\Name=Name}{\xdef\ListRedaction{\ListRedaction,\Name}}
%\DTLsavelastrowcount{\Somme}%
\StrGobbleLeft{\ListRedaction}{1}[\ListRedaction]

\def\SumRedaction{0}
%\SumRedaction

%%
\def\ListReason{}
\DTLforeach[\DTLiseq{\Competence}{Reason}]{dbScore}{\Competence=Competence,\Name=Name}{\xdef\ListReason{\ListReason,\Name}}
%\DTLsavelastrowcount{\Somme}%
\StrGobbleLeft{\ListReason}{1}[\ListReason]

\def\SumReason{0}
%\SumReason

\DTLdisplaydb{dbScore}

\begin{tabular}{cccc}
\hline
Competence&Équations&Page&\\
\hline
Analyse&\Cref{\ListAnalyse}&\Cpageref{\ListAnalyse}&\FPeval\Total{round(100*\SumAnalyser/\Somme,0)}\SI{\Total}{\percent}\\
\hline
Reason&\Cref{\ListReason}&\Cpageref{\ListReason}&\FPeval\Total{round(100*\SumReason/\Somme,0)}\SI{\Total}{\percent}\\
\hline
Redaction&\Cref{\ListRedaction}&\Cpageref{\ListRedaction}&\FPeval\Total{round(100*\SumReason/\Somme,0)}\SI{\Total}{\percent}\\
\hline
\end{tabular}

\end{document}`