Spotfire Developer: July 2014

Parsing String Dates and Changing locale

from Spotfire.Dxp.Data.DataType import Date

#this will parse ok
print Date.Formatter.FormatString
print Date.Formatter.Parse("21Jun2000")
dt = Date.Formatter.Parse("7/28/2014")

#this line will crash
#print Date.Formatter.Parse("28/7/2014")

#change the correct culture
from System.Globalization import CultureInfo
parser = Date.CreateCultureSpecificFormatter(CultureInfo("es-MX"))

#now is safe to parse
print parser.Parse("28/7/2014")
print parser.Parse("27-10-2014")
print parser.Parse("DEC31-2014")

#current date and time
from System import DateTime
now = DateTime.Now
today = DateTime.Today

#format date
dt = Document.Properties["selectedDate"] #01/15/2019
print dt.ToString("MMM yy") #shows Jan 19

Rename an Action Control Button with a Document Property value

In this example, we want an Action Control Button that will toggle some Boolean Document Property. The state of the Document Property will be shown as the label of our Button Action Control.

Since I did not find an easy way to rename an Action Control Property using the API, I had to come with a work around by using some javascript.

To rename the button with a Document Control Value, put a Label Property Control on a Text Area next to your Button Action Control. Make sure you have a Document Property called 'toggle' set to False

html:

</DIV>

</DIV>

note: you can hide the label by changing display:block to display:none

javascript (newer verisons)

//get the document property value

myProperty=$("#myProperty").text()

//rename the button (you can change other properties too)

control = $("#myControl input")

control.val(myProperty)

javascript (older version):

//get the document property value

myProperty=document.getElementById("myProperty").innerText

//rename the button (you can change other properties too)

control = document.getElementById("myControl").firstChild

control.value = myProperty

//control.style.width = "100px"

//control.style.backgroundColor = "red"

//control.style.color = "yellow"

To change the document property and then trigger the javascript, via iron python, then use this code on the Action Control button:

#toogle the Boolean Document Property

Document.Properties["toggle"] = not Document.Properties["toggle"]

#Trigger the javascript on the text area after clicking

from Spotfire.Dxp.Application.Visuals import HtmlTextArea

vis.As[HtmlTextArea]().HtmlContent += " "

Two popup calendars (start and end date)

Previous post shows how to create a popup calendar. This post shows two popup calendars.

html

Date1: <span id='dt1'><SpotfireControl id='Inpu7C0n7r01...'/></span>

Date2: <span id='dt2'><SpotfireControl id='Inpu7C0n7r02...'/></span>

js
//constraint date2 calendar based on selection and update property controls automatically
function picker1_onSelect(selectedDate,inst){

//min date constraint based on other picker
minDate = $(this).datepicker("getDate")
$("#datePicker2").datepicker("option","minDate",minDate);

//update document property after selection
$("#dt1 input").focus();
$("#dt2 input").focus();
}

//update document property after selection
function picker2_onSelect(selectedDate){
$("#dt2 input").focus();
$("#dt1 input").focus();
}

//global datepicker options
pickerOptions = {
showOn: 'button',
buttonImageOnly: true,
buttonImage: 'http://kalender.isetegija.net/Styles/SandBeach/Images/DatePicker.gif',
minDate: "-36M", maxDate: "+0D",
changeMonth: true,
changeYear: true
}

//create first date picker
document.getElementById('dt1picker').innerHTML="<input type='hidden' id='datePicker1' value="+$('#dt1Label').text()+">"
$("#datePicker1").datepicker(pickerOptions);
$("#datePicker1").datepicker("option",{altField:"#dt1 input", onClose:picker1_onSelect})

//create second date picker
document.getElementById('dt2picker').innerHTML="<input type='hidden' id='datePicker2'value="+$('#dt2Label').text()+">"
$("#datePicker2").datepicker(pickerOptions);
$("#datePicker2").datepicker("option",{altField:"#dt2 input", onClose:picker2_onSelect})

Get min and max values from a Date RangeFilter or entire data set

#get list of filtered rows
dt = Document.ActiveDataTableReference
rows = Document.ActiveFilteringSelectionReference.GetSelection(dt).AsIndexSet()

#get min and max numeric values from the set
min = dt.Columns["Date"].RowValues.GetMinNumericValue(rows.First)
max = dt.Columns["Date"].RowValues.GetMinNumericValue(rows.Last)

#or you could get the min and max formatted values from the set

min = dt.Columns["Date"].RowValues.GetFormattedValue(rows.First)
max = dt.Columns["Date"].RowValues.GetFormattedValue(rows.Last)

#or you could get the min and max values from the entire dataset

min = dt.Columns["Time"].RowValues.GetMaxValue()

max = dt.Columns["Time"].RowValues.GetMinValue()

Set Date RangeFilters porgramatically

from Spotfire.Dxp.Application.Filters import RangeFilter, ValueRange
from Spotfire.Dxp.Data.DataType import Date

#get a reference to a filter as checkbox from the myDataTable script parameter
filt=Document.FilteringSchemes[0].Item[myDataTable].Item[myDataTable.Columns.Item["Filter Date"]].As[RangeFilter]()

#reset Filter
filt.Reset()

#set min/max filter dates (same as filt.Reset())
filt.ValueRange = ValueRange(filt.ValueRange.Low,filt.ValueRange.High)

#set filter with custom dates
dt1 = Date.Formatter.Parse("1/15/2014")
dt2 = Date.Formatter.Parse("7/16/2014")
filt.ValueRange = ValueRange(dt1,dt2)

Fill blanks with last known value

This custom expression will fill the blanks with last valid value. Will replace blanks

Last([B]) over (allPrevious([A])) as [C]

If you have only one row with blanks or you don't want to rely the value on any other column then create a calculated column with 'rowid() as [A]' and use the same expression as above.

Data Table to JSON

Sometimes you want to serialize the data table into a JSON string so you can feed javascript widgets that require such format. In this example, we get the filtered rows from a given data table and generate it's JSON

Consider the following input table

A	B	C
A1	B1	C1
A2	B2	C2

The output should look like this after running the script:

[{'A':'A1','B':'B1','C':'C1'},{'A':'A2','B':'B2','C':'C2'},{'A':'A3','B':'B3','C':'C3'}]

#get filtered rows
#dataTable can be a Script parameter pointing to a specific Data Table. In this case, we use the current one

dataTable = Document.ActiveDataTableReference

#get rows and columns from our selection
rows = Document.ActiveFilteringSelectionReference.GetSelection(dataTable).AsIndexSet()
cols = dataTable.Columns

#generate our "json" data
data=[]
for r in rows:
item={}
for c in cols:
item[c.Name] = c.RowValues.GetFormattedValue(r)
data.append(item)

#convert json to string
import clr
clr.AddReference('System.Web.Extensions')
from System.Web.Script.Serialization import JavaScriptSerializer
json=JavaScriptSerializer().Serialize(data)

#save to document property to use later in javascript

Document.Properties["json"]=json

Note: If you want to limit the number of columns in your output, you can use the FindAll method. Suppose you have many columns that contains the word Project (Project Name, Project Date, Big Project, Small Project and you also want to include the Budget column, then you can use the Spotfire Search Syntax

for c in cols.FindAll("*Project* OR Budget"):
print c

Parsing Dates and calculating Last day of month with a Custom Expression

I have two columns with string dates called Start month and end Month as "yyyy-mm" but I need to convert them into a Date data type. for that, I add a Transformation or Calculated column:

The Start month is easy to parse:

Date(Integer(left([Start Month],4)),Integer(right([Start Month],2)),1)

For the end month, I have to add a month and extract one day after I parse the date:

DateAdd('day',-1,DateAdd('month',1,Date(Integer(left([End Month],4)),Integer(right([End Month],2)),1)))

Customer Banners (Ads) - SpiceUp. AX and SpotfireX Disclaimer