Search code examples
sqlfilterelementspotfire

Spotfire - Information Link - Filter Not Working

I am a beginner in Spotfire. I developed a simple information link.

Steps

  1. I Created 2 tables by adding columns.
  2. Then created Joins. 3 simple inner joins on the above table. The reason for 3 joins is it makes the query run faster than only 1 join.
  3. Then created an information link by adding elements and joins.

This works perfectly well. The data is fetched properly. But as soon as I add filter, it stops working.

I tried

  1. Creating Filter -> and then adding as element to the information link
  2. Adding filter in the column filter itself: Column E_ID - Expression %1 = 1000
  3. Editing the sql query in the information link. I added one more clause in the where section: AND E1."E_ID" = 1000

None of these work. If I remove the filter, its working perfectly fine. The filter is on the same column on which on of the join is based.

Please suggest where I am making mistake.

Solution

  • Too long to comment...

    So, I've noticed joins in the information designer can be cumbersome. It's convenient for people who don't have access to the data source, but if you do have access to the data source (as you do in this scenario), I would handle all of the logic on the DB server side. Thus, you are just supplying Spotfire with a flat file which it can easily ingest and create visualizations on. This will prevent Spotfire from bogging down with data transformations as well.

    With that being said, I would also recommend using Stored procedures to serve up the data to Spotfire. Here are a couple of answers I posted on why which will make your life easier.

    https://stackoverflow.com/a/38247931/6167855

    https://stackoverflow.com/a/39640197/6167855

    https://stackoverflow.com/a/43523380/6167855

    https://stackoverflow.com/a/38247931/6167855