489views

Filter element to restrict recent days data in Information Link - Using a date column in Sting type

#filter

#string

I wanted to create an information link using Athena JDBC drive from the Athena source table.

I have a "date" field which is of type "String" in the table. I am able to cast the type as Date in the Spotfire while building the dashboard.

But when I try to get in only the recent few days data(say 10 days) into the information link using the Filter element, I face an issue. Filter element provides an option to include only the data source fields and not any other elements already created. As the date field in the table is of type string, I am unable to use that effectively in the filter element. Even when I try cast method or as DATE stuff in the expression it does not help me in this case.

Jun 07, 2018 - 10:40am

saipriyadarshan.sundaramurthy

Flag for Review

+ Add a Comment

(2) Answers

Below is the content of that KB article

Changing the data type of the column element especially "date" / "datetime" used in a filter, can render the information link unusable. It will throw the following error.

===============

Error message: Could not open information link. ImportException at Spotfire.Dxp.Data: Failed to execute data source query for data source "Information Link Name".

===============

It also shows a similar exception error as below, in the details.

===============

InformationModelException at Spotfire.Dxp.Data: Failed to get data: Value 1/1/2018 cannot be converted to TIMESTAMP. (HRESULT: 80131500)

===============

Reason:

While creating such filters in the Information Links, the 'Data Type' that is set for the column element before creating the filter, will be considered as the only valid 'Data Type' for the current filter. Let's take an example scenario: Create a column element with 'Date' data type and add it to the information link as one of the filters. Now, the filter will only accept values in "Date" data type format (Ex: "1/1/2018"). If the data type of this column element is changed to something else , for example - from 'Date' to 'Date Time', and if a 'Date Time' value is entered for the filter (Ex: "1/1/2018 11:20:30 AM"), it will fail to open the Information Link and may throw the error as above.

Resolution:

If there is a need to use a new Data Type in the information link filter, the following steps need to be performed.

=> Remove the filter

=> Change the data type of the required column element

=> Save the column element

=> Create a new filter and based on the Filter Type set (Values or Range) ,set the required values accordingly

Flag for Review