Search code examples
nullconditional-statementsspotfire

TIBCO Spotifre Analyst v7.10: Delete Rows That Have Blank / Null Value in Column

I have the following table / report in Spotfire. I want to delete / remove all rows that are null (don't have value) in the "Value" column.

Id      Date        cDate       Value         
--------------------------------------------
A       10/17/2017  10/18/2017     1               
A       10/17/2017  10/14/2017         
A       10/17/2017  10/8/2017             
B       11/19/2017  11/19/2017     1      
B       11/19/2017  11/30/2017

I DO NOT want to filter for Value = 1 to achieve the above. The goal is to delete unnecessary rows.

Is this possible to do in Spotfire?

I want the resulting table /report to look like this: 

Id      Date        cDate       Value         
--------------------------------------------
A       10/17/2017  10/18/2017     1                        
B       11/19/2017  11/19/2017     1
Solution

  • Spotfire does not manage your data for you. what you're asking isn't possible.

    you have listed the solution there, however, which is to filter out rows that do not contain [Value] of 1. you can use either the Filters Panel or a Data Limiting Expression to accomplish this. if that doesn't solve your issue, please provide more information about why you think you need to physically delete the rows.

    EDIT

    with the extra info, there's a bit of a hacky solution you can use. again I will stress that the correct way to solve this problem is at the data level; Spotfire is not here to manage your data, rather to help make sense of it :)

    but you can reproduce the results you want for the data set you posted by adding a pivot transformation (Insert»Transformations..., then choose Pivot) configured like in this screenshot:

    enter image description here

    once you've pivoted to eliminate the other rows, you'll be able to join columns as you like.

    NOTE: this may not work in a larger data set! it depends on what you're doing with the dates. you can play around with the aggregation (e.g., using Last() rather than First()) but, and sorry to harp on it, this isn't the right way to solve this problem.