Jump to content

How to generate a table in spotfire from a given table where i need to know from which date and Hour did the power was 0 and till what date and time did it continuously was 0.

tushar lohit

Recommended Posts

The sample data table is attached the sample data shows we have two locations and two type of meter and two different dates and 5 hours of data. Need to generate a table which can differentiate between the parameters mentioned above and provide with a table to show for when to when the power was showing 0.

Link to comment
Share on other sites

Hi Tushar,

Once you uploaded the dataset in Spotfire you can follow these steps in the Data Canvas:

  • Add a “Calculate new column” transformation and create a new column (I called it “PowerZero”) by adding the expression: [Power] = 0
  • Add a “Filter rows” transformation by adding the expression: [PowerZero] = TRUE
  • Create new columns called “From date”, "From hour", "End date" and "End hour" by adding these expressions respectively:
Min([Date]) OVER ([Location],[Meter Name],[Date])Min([Hour]) OVER ([Location],[Meter Name],[Date])Max([Date]) OVER ([Location],[Meter Name],[Date])Max([Hour]) OVER ([Location],[Meter Name],[Date])
  • Exclude the columns Date, Hour, Power and PowerZero
  • Create a new column called “RemoveRows” by adding the expression:
If(Rank(Rowid(),"asc",[Location] & [Meter Name] & [From date])=1,"1st Record","Duplicate Record")
  • Add a “Filter rows” transformation adding the expression [RemoveRows] = '1st Record'
  • Exclude the "RemoveRows" column

After all these steps you should get the table that you were looking for:

image.png.a8caff1a5f787c96ad27becb43c08d08.pngI hope this is helpful, and if not, please let me know.


Link to comment
Share on other sites

  • 2 weeks later...

Hi Julieta,

Thank you for the response. It did help me get the expected answer. However when i try this logic where instead of Hour i have time stamp it doesn't work. As time stamp is a unique value functions like min and max in the logic are not giving the expected answer.


Tushar Lohit

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...