How to use SQL in Data Function
Can we write SQL in data functions?
What are the requisites for that?
Here a latest community article describing simple steps
0 Comments+ Add a Comment
Your question is quite generic, so I will give you some pointers.
1. You can write SQL in Spotfire data connectors or using Spotfire information links.
2. TERR can also run an SQL in a datasource. You can see how in this video, minute 23:30 to 27:30.
3. You can also do SQL on TERR data frames by using the package sqldf. For example,
sqldf(‘select age, count(*) from titanic3 where age is not null group by age’)
It would be useful if you could provide more information.
Guys.. anyone.. any update?
I found that sqldf was not installed. Hence I was facing issues.
Anyways got that sorted.
I am now writing an sql where in I need to store peceding 12 weeks data with current week to get a 13 week clubbed value.
My SQL goes something like this
#Package to run sqls library(sqldf) #Input data frame op1 <- sqldf("SELECT Prod_parnt,prodct_grop,year,month,week, count(distinct id) as prd_cnt, Sum(Count(distinct id)) over (partition by modlty,prodct_grop order by year,month,week rows between 12 preceding and current row) as cumu_prd_cnt, avg(rate) as sal_rate FROM ip1 group by Prod_parnt,prodct_grop,year,month,week")
Without the cumu_prd_cnt field the code works.
When I add that it gives me :
""TIBCO Spotfire Statistics Services returned an error: 'Error: error in statement: near "(": syntax error'."
can you please check and tell me where I am going wrong.
Also, don't forget to install a driver for the dbms you select.
Tools --> TERR Tools / Package Management should get you to a dialog to install sqldf and other CRAN packages
Thanks for the reply.
I wanted to know the pre-requisites and how to write SQL in Data functions (meaning TERR)
OK. So I am trying to write the below mentioned SQL in the data functions window.
op1<-sqldf("SELECT prod,sub_prod,year,month,week,count(id)/avg(rate) cnt
group by prod,sub_prod,year,month,week")
whre ip1 is the data table and op1 is the new data table that I expect to be created.
However when I hit refresh it says sqldf package does not exist.
I am currently trying to contact my admin to see if the R packages are installed properly.
I wanted to know the proper syntax of how to write sql and what library packages are required to be able to do the same