Perorming SQL Selects on R Data frames

You can write SQL queries to manipulate data frames in R with sqldf package.
Its like treating the Dataframe as if they were on Relation Database.

So you can import the Spotfire Data table into R as Data frame and on top of that you can perform SQL Queries.


Example:

library(sqldf)
otable<-sqldf('select * from itable')


Below are the packages needs to be installed on R server.

• chron_2.3-47
• DBI_0.3.1
• gsubfn_0.6-6
• proto_0.3-10
• RSQLite_1.0.0
• sqldf_0.4-10

RODBC connection R - Spotfire

Below is the R Script for connecting to ODBC using R language:-

library("RODBC")
ch<-odbcConnect("smart", uid = "user_id", pwd = "pwd",believeNRows = FALSE)
sqlTables(ch)
odbcGetInfo(ch)
sqlStr<-"select * from `Test`"
sqlQuery(ch, paste(sqlStr))

Now you can export this data frame into Sptofire Data Table and can be used for Reporting.

Fuzzy Search Join DataTables in Spotfire - Unstructured Data

Lets assume we have Two different sets of data from two different sources which are imported into Spotfire and would like to make join between these data sets. Assuming one data set is Structured data and the other is unstructured data (data from Web).

Well, we know that to combine the two different sets, would need to identify the matching key. Since we are working with Structured and Unstructured data, in most of the cases KEY will not match exactly.The only thing you can combine the two sets is by trying to match based on similarity of the column values.

For example:-
You have a company name called "Google Inc Company" on one source and 
"Google Inc Co." on other hand... You know its the same company name but how can you combine the data in Spotfire?

How to solve?
- Manually Fix the data and prepare Mapping Table
- Making use of Regex in Spotfire which we would be difficult to identify the pattern and come up with expression as the data is unstructured.
- Fuzzy String Matching using R: Easy to implement

Fuzzy String Search:
Fuzzy string approach is basically comparing the two strings based on the similarity.

Using agrep function in R, we can combine the data.

Fuzzy string matching doesn't know anything about your data but you might do.
Based on your data, we would need to set the values for the attributes like ignore.case , max distance, etc.

Details for these Attributes can be found at below location:-
https://stat.ethz.ch/R-manual/R-devel/library/base/html/agrep.html

Below is the R Script to join Two Data Tables based on Fuzzy Search String.

sec<-Table1     # Data Set1
dwh<-Table2 # Data Set2
dsecurity<-dwh[,6]  # Key column in Data Set1
ciknames<-sec[,2] # Key column in Data Set2
len<-length(dsecurity) # Number of Rows
for(i in 1:len) # For loop to search for each string
{
matches<- agrep(dsecurity[i],ciknames,max=0.2, value=TRUE, ignore.case = TRUE) # Capturing the Strings which are similar by using agrep function
matcount<-length(matches) # Count of matching strings from Data Set2 
concat<- paste(matches,collapse="|") # Concatenating the matchings Strings from Data Set 2
dwh$SECLOOKUP[i]<-concat # Adding a column to the data frame
for (j in 1:matcount)  # For loop to implement the logic for Left Outer Join
    {
        row <- matches[j]
        if(j==1)
        {
        dwh$SECLOOKUP[i]<-row
        }
        else
        {
         newRow<-dwh[i,]
         newRow[8]<-matches[j]
         dwh<-rbind(dwh,newRow)       
        }
    }
}
Otable<-dwh  # Final Data Frame with left outer join

Spotfire Library Location for Content Migrating

In Spotfire 6.5 Version, a new feature has been provided to update the location of  Library Directory.

In earlier versions of Spotfire when we Export the content from Library Administration, the zip files used to get generated on the Spotfire server at location as below

C:\tibco\tss\6.5.1\tomcat\application-data\library

The problem here is when we would like to export the content from Clustered environment to another environment, the library content zip file would be generated only on one of the clustered server. For which, you would need to check manually on which server the Zip files are generated and then copy them to the Other environment Server and import them accordingly.

 

 

 Instead of default library location, we can make use of shared location so that it can be accessed by multiple environments which avoids manual work to look on which server the zip files are generated. Below are the steps to update the same:-

- Open the Config file (Start-> Tibco Spotfire Server 6.5 -> Configure TIBCO Spotfire Server) on Spotfire server

- Provide the Bootstrap password

- Navigate to Configuration tab

- Go to Library Directory module and update and library path to use as Custom and then provide the location accordingly.

 

 

 Update this location on all the environments. The other advantage of this is, its not required to connect to server each time for migrating the content :)

Installing R External Packages

On the Statistics Server, go to the path where statistics engine is installed. 

In my case it is installed at below location:-

C:\Program Files\TIBCO\statsvcs65\SplusServer\engines\Terr\bin\x64

Open TERRconsole.exe and type below command

install.packages("XML")

In case, you would like to install the packages from Zip file downloaded from site http://cran.r-project.org then you can type the below command

install.packages(file.choose(), repos=NULL)

Verify the changes where all the external files are added to the below location or not.

C:\Program Files\TIBCO\statsvcs65\SplusServer\engines\Terr\library

For Spotfire Professional version, follow the similar steps by running Statistics Console command prompt from location  

C:\Program Files (x86)\TIBCO\Spotfire\6.5.0\Modules\TIBCO Enterprise Runtime for R_2.5.0.23\engine\bin\x64

                                                              (OR)

Copy the External library folder from Server to local machine library location (C:\Program Files (x86)\TIBCO\Spotfire\6.5.0\Modules\TIBCO Enterprise Runtime for R_2.5.0.23\engine\library)

PS: Try to test by writing some basic R scripts and run the same. 

Show/Hide Input Text Box based on Dropdown Selection using Jquery

Using Jquery we can show/hide the input box based on the Spotfire Drop Down selection.

Lets assume we have created a Drop-Down Property control with Fixed Values
as Enable and Disable as below



  
Also, create a Input property control.

Drop -Down Property Control ID: 163473c4bab648778b3e64c40463e3f  
Input Text Box Control ID:  c8b6b12144864ec5889005c6260a4b08

Now our aim is to show/hide the Input Text box based on the Drop down selection.


The default HTML would look like below:-
 

Click on JSscript Button and create below script as below:-
 

  

JScript will triggered whenever the Drop Down selection is changed.

Below is the Script.

//Triggering the script if Drop Down selection is varied

$("#163473c4bab648778b3e64c40463e3fc").change(function()

{
    var value = $("#163473c4bab648778b3e64c40463e3fc").val();  //To capture the index
    var vt = $("#163473c4bab648778b3e64c40463e3fc option:selected").text(); //To Capture the  selected text
    if(vt== "Enable")   //If the Drop Down selected value is Enable
    {
        $("#c8b6b12144864ec5889005c6260a4b08").show(); //Making the Input Box visible
    }
    else
    {
        $("#c8b6b12144864ec5889005c6260a4b08").hide(); //Making the Input box element hidden
    }
}

);