Secrets to Modifying Spotfire Information Designer SQL

  • Are you struggling to modify SQL in the Information Designer?
  • Do you need to concatenate two integer columns into a date but don’t know how to do it?

Modifying SQL in the Spotfire Information Designer can be very frustrating.  There are unwritten (to my knowledge anyway) rules that must be followed or else the query will return errors.  Recently, I wrangled with a data set whose production date was split into two columns called Year and Month (see below). I have no idea why it was built this way, but it’s what we had to work with.

If left alone, every user would have to add a calculated column or transformation to concatenate Year and Month, which is annoying and a giant waste of time when I think about how many engineers do this.  I will admit that I did leave it alone for quite a long time (see the previous comment about unwritten rules).  However, it is possible to modify the SQL to return a date in the format of mm/dd/yy (also shown above).

The SQL

The SQL code to concatenate is only one line of code:

 convert(date,concat(S1.”Month”,’-‘,01,’-‘, S1.”Year”)) as “DATE” 

 

For my use case, the SQL code looks like this before I modify it:

SELECT
S1.”WellAPI” AS “WELLAPI”,
S1.”WellName” AS “WELLNAME”,
S1.”Month” AS “MONTH”,
S1.”Year” AS “YEAR”,
S1.”CompletionDate” AS “PRODUCTIONDATE”,
FROM
“IHS”.”dbo”.”ProductionMonthlyView” S1
WHERE
<conditions>

After modification, it looks like this:

SELECT
S1.”WellAPI” AS “WELLAPI”,
S1.”WellName” AS “WELLNAME”,
S1.”Month” AS “MONTH”,
S1.”Year” AS “YEAR”,
convert(date,concat(S1.”Month”,’-‘,01,’-‘, S1.”Year”)) as “PRODUCTION DATE”

FROM
“IHS”.”dbo”.”ProductionMonthlyView” S1
WHERE
<conditions>

The Secrets

There are a few secrets you need to know to modify the SQL for your own use case…

  1. To make this work, create a duplicate column element to serve as a placeholder or a container for the concatenated date.
    • Create a duplicate column element from the same table/view as the other columns (for simplicity).
    • Make sure that the duplicate column element is of the data type that you want to wind up with. In this example, I duplicated the CompletionDate column.  If I had duplicated a column of Real or Integer data type, it would not work.
      • NOTE: I named it ProductionDate to delineate it from the actual CompletionDate column element.
    • When, I saved the column element, I save it as ProductionDate.  I did NOT modify the AS statement.  Modifying the AS statement won’t change the column name.
  2. The number of columns elements in the Information Designer must equal the number of columns specified in the SQL.  On my first attempt, I added Year and Month via the GUI, and then I added a separate line of SQL code to concatenate those two column elements, but that will not work.  You have to have the column element as a placeholder or container. The screenshot below shows this failure.
  3. Even though I do not need the Month and Year column elements, they must still be added to the information link to use them in SQL.

Summary

In summary, the steps are simple…

  1. Add all the desired columns to your information link in the Information Designer.
  2. Create a duplicate or dummy column element and add it to the information link.
  3. Modify the SQL by removing the reference to the dummy column and replacing it with the SQL line of code to concatenate.

DONE!

While writing this post, I realized there are quite a few secrets to the Information Designer.  I feel an Information Designer series coming on….after I get the other 7 posts I have sitting in draft to the finish line.

Spotfire Version

Spotfire version 7.9.1 was used to develop this content.

Guest Spotfire blogger residing in Whitefish, MT.  Working for SM Energy’s Advanced Analytics and Emerging Technology team!

5 thoughts on “Secrets to Modifying Spotfire Information Designer SQL

  1. Thanks for the tips Julie! I will say that I’ve found Spotfire InfoLinks to be pretty frustrating myself. Definitely agree there’s an unwritten code for them. One especially annoying feature is that once I modify the SQL for a link, I can’t ever go back and add new column elements. It doesn’t matter if I add the new element just in the SQL, or just in the designer, or in both, it always breaks. Have you found this to be the case?

    1. Yes, that is annoying. What I do for this is …
      1. Open the info link, go to the SQL code and copy it.
      2. Add the column elements that you want to add (SQL won’t update)
      3. Go back to the SQL code and click the Revert SQL button (this will pick up the new column elements)
      4. Copy and paste whatever you modified (such as custom where clauses or group bys)

      The other option is to manually modify the SQL for the new column elements.

  2. The supported approach is to create a new column element in Information Designer that has multiple source columns. When you do this you can then construct the row function as part of the column element.

    This leaves you with a column element that can be dropped into any Information Link without editing the SQL.

Leave a Comment

Your email address will not be published. Required fields are marked *