How to create a continuous date range given start and end dates in TIBCO Spotfire with Data Functions

How to create a continuous date range given start and end dates in TIBCO Spotfire with Data Functions

You can extrapolate new repeating rows or continuous date range rows based on either a start date or combination of start and end dates. This can be useful for easier plotting within TIBCO Spotfire.

Suppose you are given a savings rate (in $) and the date at which the savings would apply and a location ID based on which you have plot the total savings (in $) by month.

Source Table:

Loc ID	SavingsRate	Date
1	50	2/7/2018
2	60	5/3/2018
3	70	7/23/2018
4	80	8/14/2018

Expected Output Table:

Date	Savings
2/1/2018	50
3/1/2018	50
4/1/2018	50
5/1/2018	110
6/1/2018	110
7/1/2018	180
8/1/2018	260

The above output can be achieved by any of the following three approaches:

1) You can use a data function only, with 'Savings' and 'Date' as input parameters of type 'column' and 'out' as output parameter of type 'table'.

Note:  The user-contributed "lubridate" package from the open-source R community's CRAN repository (https://cran.r-project.org/) is required for running this data function. In a Spotfire desktop client session that has internet access to the CRAN repository, the "lubridate" package can be installed using the dialog that is opened by selecting "Tools > TERR Tools > Package Management" from the main menu.

library(lubridate)
stock=data.frame(Savings=Savings, Startdate=Date)
df <- data.frame(Price=numeric(),StartDate = as.POSIXlt(character())) 
row=1

while (row <= nrow(stock)) {
if(row ==1){
	st = stock[row,2]
  end=stock[row+1,2]
  price <- stock[row,1]
  values = as.Date(seq(as.Date(paste(year(st),"/",month(st),"/01",sep="")), as.Date(paste(year(end %m-% months(1)),"/",month(end %m-% months(1)),"/01",sep="")), by = "months"))
  df2=merge(price, as.POSIXlt(values))
  df <- rbind(df, df2)
  }
else if(row < nrow(stock)){
t=row
price=0
while(t>=1)
{
   price=price+ stock[t,1]
   t=t-1
}
  st = stock[row,2]
  end=stock[row+1,2]
  values = as.Date(seq(as.Date(paste(year(st),"/",month(st),"/01",sep="")), as.Date(paste(year(end %m-% months(1)),"/",month(end %m-% months(1)),"/01",sep="")), by = "months"))
  df2=merge(price, as.POSIXlt(values))
  df <- rbind(df, df2)
  }
  else{
    price=0
    t=row
    while(t>=1)
    {
        price=price+ stock[t,1]
        t=t-1
    }
    st = stock[row,2]
	values = as.Date(paste(year(st),"/",month(st),"/01",sep=""))
    df2=merge(price, as.POSIXlt(values))
    df <- rbind(df, df2)
  }
  row=row+1
}
# print(df)
colnames(df) <- c("Savings","Date")
out=df
df <- df[0,]

2) You can create a calculated column named 'SavingsRateSum' with expression:

Sum([SavingsRate]) OVER(AllPrevious([Date]))

Then execute a data function having that 'SavingsRateSum' calculated column and 'Date' as input parameters of type 'column' and 'out' as an output parameter of type 'table'.

Note:  This data function also requires the user-contributed "lubridate" package from the open-source R community's CRAN repository (https://cran.r-project.org/). In a Spotfire desktop client session that has internet access to the CRAN repository, the "lubridate" package can be installed using the dialog that is opened by selecting "Tools > TERR Tools > Package Management" from the main menu.

library(lubridate)
stock=data.frame(Savings=SavingsRateSum, Startdate=Date)
df = data.frame(Price=numeric(),StartDate = as.POSIXlt(character())) 
row=1

while (row <= nrow(stock)) {
 price=stock[row,1]
  st = stock[row,2]
if(row < nrow(stock)){
  end=stock[row+1,2]
  values = seq(as.Date(paste(year(st),"/",month(st),"/01",sep="")), as.Date(paste(year(end %m-% months(1)),"/",month(end %m-% months(1)),"/01",sep="")), by = "months")
  df2=merge(price, as.POSIXlt(values))
  df <- rbind(df, df2)
  }
  else{
    values = as.Date(paste(year(st),"/",month(st),"/01",sep=""))
    df2=merge(price, as.POSIXlt(values))
    df <- rbind(df, df2)
  }
  row=row+1
}
colnames(df) <- c("Savings","Date")
out=df
df <- df[0,]

3) If the source of the data is a relational database (such as Oracle or MS SQL Server), then it can be achieved by modifying the SQL query itself.?????? According to your requirement, you will need to modify the SQL query used by the Information Link or data connection in order to modify the data before it is imported into Spotfire. See the following knowledge base article for more details:

• KB 000037404 How to create a continuous date range given start and end dates using SQL??????

Disclaimer:
The code in this article is only a sample to be used as a reference. It is not intended to be used "As Is" in a Production environment. Always test in a Development environment. Make modifications to the code in accordance with your implementation specifications that best suit your business requirements. Refer to the reference(s) cited in this article for usage of the functions and methods used in the code.

Use of CRAN packages:
User-contributed R packages from the open-source R community's CRAN repository (https://cran.r-project.org/) are available under separate open source software license terms and are not part of the TIBCO Spotfire product line. As such, user-contributed CRAN packages are not within the scope of your license for any TIBCO Spotfire product. User-contributed CRAN packages are not supported, maintained, or warranted in any way by TIBCO Software Inc. Download and use of CRAN packages is solely at your own discretion and subject to the free open source license terms applicable to each individual package.
  Doc: How to Use Data Functions

• https://docs.tibco.com/pub/sfire-analyst/7.11.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/index.htm

External: lubridate package

• https://cran.r-project.org/web/packages/lubridate/index.html

KB: 000037404 How to create a continuous date range given start and end dates using SQL

• https://support.tibco.com/s/article/How-to-create-a-continuous-date-range-given-start-and-end-dates-using-SQL