This session of Dr. Spotfire featured DivyaJyoti Rajdev and Dr. Vishakha Mujoo, Data Scientists at TIBCO Software Inc. Discover what Dr. Spotfire's online office hours has to offer by registering for a live session. And that's not all! If you are ready to showcase interesting visuals and gain deeper insights into your data, join the conversation on Twitter using the #DrSpotfire hashtag and then post your question to the TIBCO Community "Answers" section with the hashtag #DrSpotfire. Registration information can be found at our Doctor Spotfire's Office Hours page.
Quick Reference Times
02:00 Live Demo of Kerberos Authentication in Spotfire - WIKi Article
26:00 Q1. Calculating net working hours between two datetime stamps
31:14 Q2. Convert multiple column values
40:30 Q3. Specific Expression Function for calculating net present value
48:40 Q4. How to connect two points with a line in a Scatter Plot
52:50 Q5. How to score a model in Spotfire
Question One: How to calculate net working hours between two datetime stamps?
For a specific problem like this users won’t find the exact answer online. First they will need to create the algorithm that solves the problem then using Spotfire documents and resources learn to implement in Spotfire. Steps for question will look like this:
Assume Date columns Date1, Date2, where Date1 is older than Date2
Find week of year using ‘Week()’, as K1, K2
Weekends between Date1, Date2 = K1 - K2 -1
Days in each weekend = 2
Weekend Days total between Date1, Date2 = 2*(K1 - K2 - 1) (If K1 > K2, otherwise use year and week to manually change)
Time span between Date1, Date2 = DateDiff(Date1,Date2)
Total Days between Date1,Date2 by using TotalDays() on timespan from step 6.
Working days = Remove Weekend Days as Total Days from step 7. minus Weekend Days from step 5.
Working Hours ( excluding first and last working day) = (Working days from step 8 - 2) * 10
Check whether ‘Hour()’ of Date1 > 8 and <18 if yes H1=Hour(Date1) - 8 otherwise 0
Similarly for Date2
Total working hours = Working hours from step 9 + H1 + H2
Link to Wiki Entry on Kerberos Authentication
Questions Two: How to convert multiple column values?
The user is wondering the best approach for converting multiple columns from the imperial to metric system. The first solution uses the insert transformation method. Then using the calculate and replace transformation users can use the expression language to make the conversion. The draw back for that approach is users will have to do it for each column.
The second approach involves using an expression function similar to data functions. This can be found from navigating to data functions then the expression function tab. Using the column function type you can enter the expression for the conversion and save it. Now going to insert calculated column you can select the expression function just created. The difference between these two approaches is that the first approach will stay linked including for in database. The advantage for expression functions is that they are not tied to inputs which improves the ease of reuse.
The third approach is using data functions. The advantage here is after creating the data function you can apply it to columns in batch as well as utilize advanced and more complex calculations.
Question Three: User has a calculation for net present value but it is resulting in empty values.
In order to figure out what is going wrong debugging is demonstrated. Divya Jyoti points out that the user is trying to calculate values based on a cross table in a data function. But, what the data function uses for the calculation is the underlying data table from the cross table not necessarily the values displayed.
In this situation it will be easier to use an expression function since they are not tied to your input. After creating the calculation in the expression function the user will have to specify the aggregation for the cross table by adding an as statement at the end of the expresion. Another way of using the data from the cross table and not the underlying data table is by exporting the data from the cross table visualization and using that as a new data table.
Question Four: How to connect two points with a line in a scatter plot?
From properties the user can navigate to line connection. There users can define the line connection based on columns.The points are now connected but since they are so close it is hard to see. By adding a Y zoom slider users can then zoom in and see the connecting lines.
Question Five: How to score a model in Spotfire?
Using an example model Divya Jyoti shows building a model and saving it to disc or a common network location. Next using an R script in Spotfire it can be loaded and then predicted from. The second way would be using web services and Score using REST calls. The final method is to represent the model as a platform independent PMML, POJO, PFA, etc. From there users can use an event engine such as TIBCO StreamBase to channel incoming data streams and predict.