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'
+ ")
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@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.
