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

[pasbal]

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:

Code: Select all

\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
  

  Code: Select all

  \def\Somme{0}
  \DTLforeach[\DTLiseq{\ColA}{A}]{dbTest}{\ColA=ColA}{\DTLadd{\Somme}{1}{\Somme}}
  \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

[rais]

Hi,

• 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?

• 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
  

  Code: Select all

  \def\Somme{0}
  \DTLforeach[\DTLiseq{\ColA}{A}]{dbTest}{\ColA=ColA}{\DTLadd{\Somme}{1}{\Somme}}
  \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.

• 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.

Code: Select all

\documentclass[12pt,a4paper]{article}
\usepackage[utf8]{inputenc}
\usepackage[francais]{babel}
\usepackage[T1]{fontenc}
\usepackage{datatool}
\usepackage[french]{cleveref}
\usepackage{filecontents}
% datatool loads fp package already
\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}

\DTLloaddb{dbTest}{dbTest.csv}

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

\def\SumB{0}
\DTLforeach[\DTLiseq{\ColA}{A}]{dbTest}{\ColA=ColA, \ColB=ColB}{\DTLadd{\SumB}{\SumB}{\ColB}}
\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]

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]

Hello,

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

Thanks again and regards

Code: Select all

    \documentclass[12pt,a4paper]{article}
    \usepackage[utf8]{inputenc}
    \usepackage[francais]{babel}
    \usepackage[T1]{fontenc}
    \usepackage{datatool}
    \usepackage[french]{cleveref}
    \usepackage{filecontents}
    % datatool loads fp package already
    \usepackage{siunitx}
	\usepackage{xstring}
	
    \begin{document}

   
\DTLnewdb{dbScore}


\begin{equation}
y=a\times x +3
\label{EqA}
\end{equation}
\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}



\begin{equation}
y=x \times x +3
\label{EqB}
\end{equation}
\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}

\begin{equation}
y=x  +3
\label{EqC}
\end{equation}
\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}
\DTLforeach[\DTLiseq{\Competence}{Analyse}]{dbScore}{\Competence=Competence,\Score=Score}{\DTLadd{\SumAnalyser}{\SumAnalyser}{\Score}}
%\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}
\DTLforeach[\DTLiseq{\Competence}{Redaction}]{dbScore}{\Competence=Competence,\Score=Score}{\DTLadd{\SumRedaction}{\SumRedaction}{\Score}}
%\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}
\DTLforeach[\DTLiseq{\Competence}{Reason}]{dbScore}{\Competence=Competence,\Score=Score}{\DTLadd{\SumReason}{\SumReason}{\Score}}
%\SumReason

\DTLaddall{\Somme}{\SumAnalyser,\SumRedaction,\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}