> On Sep 11, 2017, at 1:05 AM, Mangalani Peter Makananisa
> <pmakanan...@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@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
______________________________________________
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.
