在Spotfire中的同一列中匹配组(表达式) [英] matching for group (Expressions) in same column in Spotfire
问题描述
我想要实现的新功能是
- 搜索 下一个 如果TYPE = 1最接近TYPE = 0,则TYPE = 1的最近TYPE = 0,如果TYPE = 1,则在新计算列中将其标记为T,否则为NULL
-
数据规则:
- 状态列按顺序包含{1,2},默认值为th e空的空间与上面最近的Status值相同。
- 类型列仅包含0和1随机
应该是这样的:
解决方案我试过:
If(([type] = 1)和(first([type])OVER ([type]),AllNext([status])))= 0),T,Null)
看起来不错,但问题出在每个状态组中,例如第一个状态= 1组中的最后一个TYPE = 1(第五行),它没有下一个最接近的TYPE = 0,所以判断是空值。但是根据代码,它是T! ($ / $)
任何建议和意见?
非常感谢!
PS:一些细节:
- 状态列的第一个类型值为NULL
- 状态列中的其他空格可以填充如下:对于表达式有用:):
好吧,这个真的测试了我的极限,我很好奇,如果它会扩大。它适用于您的数据,给定您在哪里
NULL
。花费几个小时来弄清楚。- 插入计算列
RowId()
并将其命名为RowNum
- 插入计算列
RankReal([status],ties.method =第一个)
并将其命名为等级
- 插入计算列
If ([Status])over(Previous([RowNum])) - First([status])over([RowNum]))= 0,[Rank] - Max([RowNum])OVER(相交([status] ,AllPrevious([RowNum]))))
并将其命名为GroupOfTypes
- code> If([type]> Min([type])over(Intersect([GroupOfTypes],AllNext([RowNum]))),T)并将其命名为
标记
。这是你真正关心的那一行。
结果
说明 这完成了基本上创建一个基于伪rownumber在一段状态。你会看到它根据状态按顺序排列。这是我的方法分组数据的第一步。 第一部分 最后我们评估 Regarding the previous question which I have posted:
calculation the difference for same column for the specific rows in Spotfire I have a new problem for it, below is the sample: The new function that i want to realize is, Data Rules: output should be like this: the solution what i have tried: it looks fine, but the problem is in each status group, for example the last TYPE=1(the 5th row) in the first status=1 group, it has not next nearest TYPE=0, so the judgement would be Null. But based on the code, it is T! :( any suggestion and idea for it?
thanks a lot'!
PS: some details: Ok, this one really tested my limits and I'm curious if it will scale. It works for your data, given where you have Results EXPLANATION This is done to essentially create a pseudo rownumber based on a segment of statuses. You'll see it ranks based on the status, sequentially. It's the first step in my method to group your data. The first part of this, Lastly we evaluate if the 这篇关于在Spotfire中的同一列中匹配组(表达式)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
RankReal([status],ties.method = first)
If((first([status])over(Previous([RowNum]) ) - First([status])over([RowNum]))= 0,[Rank] - Max([RowNum])OVER(相交([status],AllPrevious([RowNum]))))
(first([status])over(Previous([RowNum])) - First([status ])over([RowNum]))= 0
正在评估上一行的 [status]
列是否与当前行相同。如果是,则返回一个布尔值 TRUE
的值。如果它与上一行相同,我们知道它属于同一个块/分组,所以我们做一些数学来标记这个列与整个块相同的值。那就是 [Rank] - Max([RowNum])OVER(Intersect([status],AllPrevious([RowNum])))
。因此,我们分组中的每一行将等于相同的值。
If([type]> Min([type])over相交([GroupOfTypes],AllNext([RowNum]))),T)
[type]
大于基于rownumber的所有下一行的mnimal [type]
。这限制了我们关注的数据到 [type] = 1
而不实际过滤行的数据,而只在数据集中查找 forward 。如果这是真的,我们用T标记。
If(([type]=1) and (first([type]) OVER (intersect(previous([type]),AllNext([status])))=0),"T",Null)
NULL
. It took a few hours to figure out.
RowId()
and name it RowNum
RankReal([status],"ties.method=first")
and name it Rank
If((first([status]) over (Previous([RowNum])) - First([status]) over ([RowNum]))=0,[Rank] - Max([RowNum]) OVER (Intersect([status],AllPrevious([RowNum]))))
and name it GroupOfTypes
If([type]>Min([type]) over (Intersect([GroupOfTypes],AllNext([RowNum]))),"T")
and name it Marking
. This is the row you really care about.RankReal([status],"ties.method=first")
If((first([status]) over (Previous([RowNum])) - First([status]) over ([RowNum]))=0,[Rank] - Max([RowNum]) OVER (Intersect([status],AllPrevious([RowNum]))))
(first([status]) over (Previous([RowNum])) - First([status]) over ([RowNum]))=0
is evaluating if the previous row's [status]
column is the same as the current row. If it is, it returns a boolean TRUE
value. If it is the same as it's previous row, we know that it belongs in the same block / grouping, so we do some math to label this column with the same value for the entire block. That is [Rank] - Max([RowNum]) OVER (Intersect([status],AllPrevious([RowNum])))
. Therefore, each row within our grouping will equate to the same value.If([type]>Min([type]) over (Intersect([GroupOfTypes],AllNext([RowNum]))),"T")
[type]
is larger than the mnimal [type]
over all the next rows, based on the rownumber. This limits the data we focus on to those where [type] = 1
without actually filtering the rows, while only looking forward in the dataset. If this is true we flag it with T.