计算Spotfire中同一列的时差 [英] calculate the time difference for same column in Spotfire

问题描述

我是Spotfire的初学者。我对某些列值的差异计算有一个问题。样本表可能是这样的：

  id时间戳状态
 1 7/1/2016 12:00:01 AM 1 
 2 7/1/2016 12:00:03 AM 0 
 3 7/1/2016 12:00:04 AM 1 
 4 7/1/2016 12:00： 06 AM 0 
 5 7/1/2016 12:00:09 AM 1 
 6 7/1/2016 12:00:10 AM 0 
 7 7/1/2016 12:00 ：12 AM 1 
  

我想计算当状态为1时的时间戳的时差，
我想要的决赛桌是：

  id时间戳状态time_diffence 
 3 7/1/2016 12:00:04 AM 1 3 
 5 7/1/2016 12:00:09 AM 1 5 
 7 7/1/2016 12:00:12 AM 1 3 
  

似乎我应该识别计算的表达式，但是我不知道为一个参数进行计算:(。有人可以帮助我吗？

还有一个小问题：如果timestamp列值只是数字值，怎么办我计算差异，有没有相关的功能像DateDiff（）这里？例如：

  id times state 
 1 12 1 
 2 7 0 
 3 10 1 
 4 11 0 
 5 6 1 
 6 9 0 
 7 7 1 
  

结果可能是：

  id times state diffence 
 3 10 1 -2 
 5 6 1 -4 
 7 7 1 1 
  

之后运行代码：我有错误如下：

如果它与上一行具有相同的时间戳，则差异将保持与之前相同，但实际上具有相同时间戳的行的差异将为0

感谢您的帮助：）

解决方案

假设您的数据按升序排序订单由 [t imestamp] 在导入之前，您可以使用上一个函数与在之间进行分区 [state] = 1 。

使用此表达式插入计算列：

  If（[state] = 1，DateDiff（ss，Min（[timestamp]）OVER（Previous（[timestamp]）），[timestamp] ））
  

您将看到它在您的表格中显示如下：

然后，如果你只想看到有提到的差异的行，在你的表你可以...

右键单击>属性>数据>使用表达式限制数据>

并插入表达式： [time_difference]> 1

这将导致此表：

I am a beginner for Spotfire. I have a problem about the difference calculation for the some column value. A sample table could be like this:

id  timestamp              state
 1  7/1/2016 12:00:01 AM    1
 2  7/1/2016 12:00:03 AM    0
 3  7/1/2016 12:00:04 AM    1
 4  7/1/2016 12:00:06 AM    0
 5  7/1/2016 12:00:09 AM    1
 6  7/1/2016 12:00:10 AM    0
 7  7/1/2016 12:00:12 AM    1

I want to calculate the time difference for the timestamp when the state is 1, the final table I want to have is:

id  timestamp              state  time_diffence
 3  7/1/2016 12:00:04 AM    1     3
 5  7/1/2016 12:00:09 AM    1     5
 7  7/1/2016 12:00:12 AM    1     3

it seems that I should identify an expression for the calculation, but I have not idea for the calculation just for one parameter :(. somebody could help me ?

still one more small question: what if the timestamp column value is just number value, how can i calculate the difference, is there any related function like DateDiff() here? for example:

id  times state
 1  12    1
 2   7    0
 3  10    1
 4  11    0
 5   6    1
 6   9    0
 7   7    1

the result could be :

id  times state  diffence
 3  10    1      -2
 5   6    1      -4
 7   7    1       1

after running the code: i have the error as below:

for the row if it has the same time stamp as the last previous row, the difference will keep same as before, but actually the difference for the rows which have same time stamp would be as 0

thanks for your help :)

解决方案

Assuming your data is sorted in ascending order by [timestamp] before you import it, you can partition using the Previous function with Over where the [state]=1.

Insert a calculated column with this expression:

If([state]=1,DateDiff("ss",Min([timestamp]) OVER (Previous([timestamp])),[timestamp]))

You will see it populated in your table like the below:

Then if you ONLY want to see the rows that have the difference you mentioned, on your table you can...

Right Click > Properties > Data > Limit data using expression >

And insert the expression: [time_difference] > 1

This will result in this table:

这篇关于计算Spotfire中同一列的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！