Search code examples
spotfire

Spotfire TERR script to check if date string is within date range

startDate="04/01/2015"
endDate="07/01/2015"
dateString="04/30/2015 03/21/2015 06/28/2015 12/19/2015"

I want to have a calculated column which returns a "Yes" if all dates in dateString are between startDate and endDate, and a "No" otherwise.

Please note: dateString can have any number of dates.

I tried writing a TERR script(return type String):

MyCustomFunction <- function(startDate, endDate, dateString) {
    v1 <- scan(text=dateString, what='', quiet=TRUE)
    v2 <- as.Date(v1, '%m/%d/%Y')
    temp <- v2 >= as.Date(startDate, '%m/%d/%Y') & v2 <= as.Date(endDate,'%m/%d/%Y')
    ifelse(length(unique(temp))==1, ifelse(unique(temp)==TRUE, test<-as.character(TRUE), test<-as.character(FALSE)), test<-as.character(FALSE))
    test
}
output <- MyCustomFunction(startDate = input1, endDate = input2, dateString = input3)

But this displays an empty column. Can anybody help me with this? Or provide an alternate solution? I use Spotfire version 6.5

Thanks in advance.

Solution

  • I adjusted your code a bit. Spotfire sets the input as a vector. Adjust inputs and outputs as you want them to be. I used strsplit because I assume the data looks like this.enter image description here

    also there is no need for assignments inside your ifelse statement. You just assign the outcome to your variable. I tested this code in Spotfire 6.5 and I get the correct results back. 

    startDate <- "01/01/2015"
endDate <- "07/01/2015"

MyCustomFunction <- function(startDate, endDate, dateString) {
      outcome <- NULL
      for (i in 1:length(dateString)) {
      v1 <- strsplit(dateString[i], " ", fixed = TRUE)
      v2 <- as.Date(unlist(v1), '%m/%d/%Y')
      temp <- v2 >= as.Date(startDate, '%m/%d/%Y') & v2 <= as.Date(endDate,'%m/%d/%Y')
      outcome <- c(test, ifelse(length(unique(temp))==1, ifelse(unique(temp)==TRUE, as.character(TRUE), as.character(FALSE)), as.character(FALSE)))
      }
      return(outcome)
}

output <- MyCustomFunction(startDate, endDate, dateString)