Business Intelligence Tools

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.

8 thoughts on “Secrets to Modifying Spotfire Information Designer SQL

  1. Sean Conroy Reply

    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?

    • Julie Schellberg of Big Mountain Analytics, LLC Post authorReply

      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.

      • Sean Conroy Reply

        Ok great – thanks for the tip. I will give that a shot.

  2. Robert Cain Reply

    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.

  3. Zoheb Reply

    Thanks Julie!
    It is really annoying that not much documentation is available on Information designer.

  4. Shubham Reply

    Hi Julie,

    Thanks for this article.
    I seek your help on below doubts related to Information Designer and it will be really helpful if you could give some lead on these.

    1. Is it possible to remove columns on an information link before loading data (without having access to information designer) ?
    2. Is it possible to aggregate data from an information link on load (instead of once the granular data is imported) ?

    Many thanks in advance.

  5. Julie Sebby Post authorReply

    Users can always use the Exclude Columns Transformation (in the Tools menu) to exclude transformations even if they don’t have access to the information designer. You can use either the on-demand functionality or a data function to aggregate data from an information link on load.

Leave a Reply

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