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]

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.

[Julieta Diaz]

Hi Tushar,

In the attached dxp you can find a solution to this question. Please let me know if it is clear and what you expected.

Best regards,

[tushar lohit]

Hi Julieta,

Thank you for the response my spotfire is 11.4 and the DXP file that you gave is version 12. Upgrading my spotfire version is not possible right now can you help me with logic in any other way.

Regards,

Tushar Lohit

[Julieta Diaz]

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:

I hope this is helpful, and if not, please let me know.

Regards,

[tushar lohit]

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.

Regards,

Tushar Lohit