Case statement in sqldf

Thanks D,

I will work on the solution you gave and give feedback.

-----Original Message-----
From: David Winsemius [mailto:dwinsemius at comcast.net] 
Sent: 11 September 2017 05:19 PM
To: Mangalani Peter Makananisa
Cc: r-help at r-project.org
Subject: Re: [R] Case statement in sqldf

On Sep 11, 2017, at 1:05 AM, Mangalani Peter Makananisa <pmakananisa at sars.gov.za> wrote:

Hi all,

I am trying to create a new  variable called Fiscal Year (FY) using case expression in sqldf  and I am getting a null FY , see the code below .

Please advise me as to how I can do this mutation.

 library(zoo)
 library(lubridate)
 library(stringr)
 library(RH2)
 library(sqldf)

cr$ReportDate = as.Date(cr$ReportDate, format ='%Y-%m-%d')

cr2 =  sqldf(" select ReportDate

+                      ,  case
+                        when ReportDate between  '2012-04-01'  and  '2013-03-31'
+                        then '2012_13'
+                        when  ReportDate between '2013-04-01'  and  '2014-03-31'
+                        then '2013_14'
+                        when  ReportDate between  '2014-04-01'  and  '2015-03-31'
+                        then'201415'
+                        when ReportDate between '2015-04-01'  and  '2016-03-31'
+                        then '2015_16'
+                        when ReportDate between '2016-04-01'  and  '2017-03-31'
+                        then '2016_17'
+                        when ReportDate between '2017-04-01'  and  '2018-03-3'
+                        then '2017_18' else null
+                        end as FY
+               from cr
+              where  ReportDate  >=  '2012-04-01'
+              ")

There was no cr object in any of the package I loaded although `lubridate` and `stringr` appear unnecessary and were omitted. I get no error with your code using this test object:

 cr <- data.frame(ReportDate = seq(as.Date("1970-01-01"), as.Date("2020-01-01"), by="1 year" ))

cr2 =  sqldf(" select ReportDate

+                      ,  case
+                        when ReportDate between  '2012-04-01'  and  '2013-03-31'
+                        then '2012_13'
+                        when  ReportDate between '2013-04-01'  and  '2014-03-31'
+                        then '2013_14'
+                        when  ReportDate between  '2014-04-01'  and  '2015-03-31'
+                        then'201415'
+                        when ReportDate between '2015-04-01'  and  '2016-03-31'
+                        then '2015_16'
+                        when ReportDate between '2016-04-01'  and  '2017-03-31'
+                        then '2016_17'
+                        when ReportDate between '2017-04-01'  and  '2018-03-3'
+                        then '2017_18' else null
+                        end as FY
+               from cr
+              where  ReportDate  >=  '2012-04-01'
+              ")

str(cr2)

'data.frame':	8 obs. of  2 variables:
 $ ReportDate: Date, format: "2013-01-01" "2014-01-01" "2015-01-01" ...
 $ FY        : chr  "2012_13" "2013_14" "201415" "2015_16" ...

Thanking you in advance

Kind regards,

Mangalani Peter Makananisa (0005786)
South African Revenue Service (SARS)
Specialist: Statistical Support
TCEI_OR (Head Office)
Tell: +272 422 7357, Cell: +2782 456 4669

Please Note: This email and its contents are subject to our email 
legal notice which can be viewed at 
http://www.sars.gov.za/Pages/Email-disclaimer.aspx

	[[alternative HTML version deleted]]

______________________________________________
R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see 
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.

David Winsemius
Alameda, CA, USA

'Any technology distinguishable from magic is insufficiently advanced.'   -Gehm's Corollary to Clarke's Third Law

Please Note: This email and its contents are subject to our email legal notice which can be viewed at http://www.sars.gov.za/Pages/Email-disclaimer.aspx