Get all values in one column associated with the unique values in another column in a Datatool database

Question

I want to get for each of the unique values of one column (eg, country) all the corresponding values of another column (eg, town). I need to use Datatool for this, as the db changes.

For instance in:

\begin{filecontents}{test.csv}
Name,Town,Country,Age
Adam,Xcity,Xland,20
Berta,Ytown,Yland,30
Cesar,Ztington,Zland,40
Dora,Ztington,Zland,20
Emil,Ytown,Yland,30
Franz,Ytown,Yland,20
\end{filecontents}

The output I am seeking is:

The cities in Italy are Rome, Milan. The cities in the US are Rome, New York. The cities in Germany are Berlin, Munich.

(I am probably not getting how to do nested DTLforeach)

Answer 1

Try this code:

\documentclass{article}  
\usepackage{datatool}

\usepackage{filecontents}
\begin{filecontents*}[overwrite]{cities.csv}
    Italy,Rome,Milan,\\
    USA,New York,Boston, \\
    Germany,Berlin,Munich,\\
\end{filecontents*} 

\newcommand{\FindCities}[1]{%
    \DTLforeach*[\DTLiseq{\Country}{#1}]{cities}{%
        \Country=Country,\BigCity=BigCity,\Other=Other}{\noindent The cities in \Country\ are  \BigCity, \Other.}
}
    
\begin{document}

\DTLloaddb[noheader,keys={Country,BigCity,Other}]{cities}{cities.csv}   
    
\FindCities{Italy}\FindCities{USA}\FindCities{Germany}

\end{document}

Answer 2

Is this what you are after?

% With older LaTeX-kernels you need to activate/uncomment the following line:
%\RequirePackage{filecontents}

\begin{filecontents*}{test.csv}
Name,City,Country,Age
Albert,Rome,Italy,20
Berta,Berlin,Germany,30
Cesar,Rome,USA,40
David,Milan,Italy,20
Emil,Munich,Germany,30
Fiedrich,New York,USA,20
Gustav,Paris,France,40
Heinrich,London,England,30
Isidor,Manchester,England,80
Jacob,Madrid,Spain,30
Karl,Milan,Italy,30
Ludwig,Barcelona,Spain,40
Marie,Fort Roughs,Sealand,40
Nathan,Sevilla,Spain,50
Otto,Berlin,Germany,50
Paul,Leeds,England,30
Quelle,Madrid,USA,50
Richard,Marseille,France,20
Samuel,Toulouse,France,40
Theodor,Berlin,Germany,30
Ulrich,Liverpool,England,10
Viktor,Lyon,France,50
Wilhelm,Glasgow,England,10
Xantippe,Milan,Italy,20
Ypsilon,Paris,France,40
Zacharias,Sevilla,Spain,50
\end{filecontents*}

\documentclass[a4paper]{article}

% ---- Layout ----------------------------------------------------------------------
%      The code for changing the layout is not of importance to you.
%      It is messy and only suits the need of having this example fit on one page
\pagestyle{plain}
\csname @ifundefined\endcsname{pagewidth}{}{\pagewidth=\paperwidth}%
\csname @ifundefined\endcsname{pdfpagewidth}{}{\pdfpagewidth=\paperwidth}%
\csname @ifundefined\endcsname{pageheight}{}{\pageheight=\paperheight}%
\csname @ifundefined\endcsname{pdfpageheight}{}{\pdfpageheight=\paperheight}%
\textwidth=\paperwidth
\advance\textwidth-3cm
\evensidemargin=\dimexpr-1in+2cm\relax
\oddsidemargin=\dimexpr-1in+1cm\relax
\marginparsep=2mm
\marginparwidth=\dimexpr2cm-2\marginparsep\relax
\textheight=\paperheight
\advance\textheight-3cm
\topmargin=\dimexpr-1in+1cm\relax
\headheight=0pt
\headsep=0pt
{\normalfont
 \setbox\csname @tempboxa\endcsname\hbox{0123456789}%
 \global\footskip=\dimexpr 1cm -.5\dp\csname @tempboxa\endcsname
                               +.5\ht\csname @tempboxa\endcsname\relax
}%
\parindent=0pt
\flushbottom
% ---- END OF LAYOUT ---------------------------------------------------------------

\usepackage{datatool}

\newcommand\scratchlist{}%

\makeatletter
\newcommand{\FindFields}[7]{%
   %#1 - database
   %#2 - column/data-field whose value to spit out
   %#3 - column/data-field whose value must fit
   %#4 - value that must fit
   %#5 - phrase in case no element is found
   %#6 - phrase in case only one element is found
   %#7 - phrase in case more than one element is found
   \begingroup
   \gdef\scratchlist{}%
   \DTLlistskipemptytrue
   \DTLforeach*[\DTLiseq{\foo@reserved}{#4}]{#1}{\foo@reserved=#3,\bar@reserved=#2}{%
     \protected@edef\bar@reserved{\bar@reserved}%
     \expandafter\DTLifinlist\expandafter{\bar@reserved}{\scratchlist}{}{%
       \edtlinsertinto{\bar@reserved}{\scratchlist}{\dtlcompare}%
     }%
   }%
   \DTLifnullorempty{\scratchlist}{#5}{%
     \begingroup
     \DTLnumitemsinlist{\scratchlist}{\scratchlist}%
     \expandafter\endgroup\ifnum\scratchlist=1 %
     \expandafter\@firstoftwo\else\expandafter\@secondoftwo\fi{#6}{#7}%
     \renewcommand\DTLlistformatitem[1]{##1}%
     \renewcommand\DTLlistformatsep{,\space}%
     \renewcommand{\DTLandname}{and}%
     \renewcommand\DTLlistformatlastsep{\space\DTLandname\space}%
     %\renewcommand{\DTLlistformatoxford}{,}%
     \DTLformatlist{\scratchlist}%
   }%
   \endgroup
}%
\makeatother

\DTLloaddb[keys={name,cities,countries,age}]{database}{test.csv}
    
\begin{document}

The database is:

\vfill

\noindent\DTLdisplaydb{database}

\vfill

\hrule\medskip

\FindFields{database}{cities}{countries}{Italy}%
{There are no cities of Italy listed in the database}%
{The only city of Italy listed in the database is }%
{Cities of Italy listed in the database are }.
       
\FindFields{database}{cities}{countries}{USA}%
{There are no cities of the US listed in the database}%
{The only city of the US listed in the database is }%
{Cities of the US listed in the database are }.

\FindFields{database}{cities}{countries}{Germany}%
{There are no cities of Germany listed in the database}%
{The only city of Germany listed in the database is }%
{Cities of Germany listed in the database are }.

\FindFields{database}{cities}{countries}{France}%
{There are no cities of France listed in the database}%
{The only city of France listed in the database is }%
{Cities of France listed in the database are }.

\FindFields{database}{cities}{countries}{England}%
{There are no cities of England listed in the database}%
{The only city of England listed in the database is }%
{Cities of England listed in the database are }.

\FindFields{database}{cities}{countries}{Spain}%
{There are no cities of Spain listed in the database}%
{The only city of Spain listed in the database is }%
{Cities of Spain listed in the database are }.

\FindFields{database}{cities}{countries}{Sealand}%
{There are no cities of Sealand listed in the database}%
{The only city of Sealand listed in the database is }%
{Cities of Sealand listed in the database are }.

\FindFields{database}{cities}{countries}{Wonderland}%
{There are no cities of Wonderland listed in the database}%
{The only city of Wonderland listed in the database is }%
{Cities of Wonderland listed in the database are }.

\medskip\hrule\medskip

\FindFields{database}{countries}{cities}{Rome}%
{There are no countries having a city named Rome listed in the database}%
{The only country having a city named Rome listed in the database is  }%
{Countries having a city named Rome listed in the database are }.

\FindFields{database}{countries}{cities}{Madrid}%
{There are no countries having a city named Madrid listed in the database}%
{The only country having a city named Madrid listed in the database is  }%
{Countries having a city named Madrid listed in the database are }.

\FindFields{database}{countries}{cities}{Munich}%
{There are no countries having a city named Munich listed in the database}%
{The only country having a city named Munich listed in the database is  }%
{Countries having a city named Munich listed in the database are }.

\medskip\hrule\medskip

\csname @for\endcsname\Age:=10,20,30,40,50,60,70,80\do{%
  \FindFields{database}{name}{age}{\Age}%
  {There are no persons of age \Age\space listed in the database}%
  {The only person of age \Age\space listed in the database is }%
  {Persons of age \Age\space listed in the database are }.%
  \par
}

\medskip\hrule\medskip

\csname @for\endcsname\Country:=England,France,Germany,Italy,Sealand,Spain,USA,Wonderland\do{%
  \FindFields{database}{name}{countries}{\Country}%
  {There are no persons living in \Country\space listed in the database}%
  {The only person living in \Country\space listed in the database is }%
  {Persons living in \Country\space listed in the database are }.%
  \par
}

\medskip\hrule\medskip

Don't use this for looking up people living in Rome or Madrid, because according to
the database there might be several cities of that name. You may need to call
\verb|\DTLforeach| with a more sophistcated ifthen-condition-expression in its optional
argument for checking whether combinations of several conditions are fulfilled.

\end{document}