An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20111127/f9a51dd3/attachment.pl>
sqldf if iif
2 messages · Carlos Rivera, 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.