[R] Case statement in sqldf

[Mangalani Peter Makananisa]

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

	[[alternative HTML version deleted]]