[R] sqldf if iif

[Jeff Newmiller]

sqldf uses the SQLite database by default for backend processing. The "iif" function is specific to the Jet database engine syntax (which underlies MS Access). You could read up on SQLite syntax, or you could avoid using nonstandard SQL syntax, retrieve the data into a data frame, and use R code to do your logical merging into one column.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

Carlos Rivera <limnorivera at gmail.com> wrote:

>Dear all,
>
> 
>
>I have problems with iif function using sqldf library.
>
>I counted abundance (Num) of different "SPECIES" in two moments (esf)
>saving
>the information in two Tables (esf50, esf100):
>
>esf50
>
>SAMPLE      SPECIES      Num             esf
>
>1289            diso1           44                  50
>
>1289            diso2           5                     50
>
>1289            diso3           1                     50
>
>9999            diso1           44                  50
>
>9999            diso2           5                     50
>
>9999            diso3           1                     50
>
>                                                                       
>
>esf100                                
>
>SAMPLE      SPECIES      Num             esf
>
>1289            diso1           82                  100
>
>1289            diso2           13                  100
>
>1289            diso3           2                     100
>
>1289            diso4           3                     100
>
>9999            diso1           82                  100
>
>9999            diso2           13                  100
>
>9999            diso3           2                     100
>
>9999            diso4           3                     100
>
> 
>
>I would like subtract column "Num" between the two moments considering
>only
>the changes, therefore I use the conditional if:
>
> 
>
>var100<-sqldf("select esf100.SAMPLE, esf100.SPECIES, esf100.Num,
>esf100.esf,
>
>                  iif esf100.Num - esf50.Num >=0, esf100.Num-esf50.Num,
>esf100.Num as PIPAS 
>
>                   from esf100 left join esf50 on esf100.SAMPLE =
>esf50.SAMPLE
>
>                   and esf100.SPECIES = esf50.SPECIES")
>
> 
>
>I think the structure is right because the SQL query run ok in Access.
>Is
>the if syntax the problems?
>
> 
>
>Thank in advanced.
>
> 
>
>Best wishes,
>
> 
>
>Carlos Rivera
>
> 
>
> 
>
> 
>
> 
>
>
>	[[alternative HTML version deleted]]
>
>______________________________________________
>R-help at r-project.org mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.