[R] Case statement in sqldf
Mangalani Peter Makananisa
pmakananisa at sars.gov.za
Mon Sep 11 10:05:52 CEST 2017
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.
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'
+ 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
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]]
More information about the R-help