在Spotfire中的同一列中匹配组（表达式） [英] matching for group (Expressions) in same column in Spotfire

问题描述

关于我发布的上一个问题：

我想要实现的新功能是

• 搜索 下一个
  
• 
  

数据规则：

• 状态列按顺序包含{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 。花费几个小时来弄清楚。

1. 插入计算列 RowId（）并将其命名为 RowNum


2. 插入计算列 RankReal（[status]，ties.method =第一个）并将其命名为等级


3. 插入计算列 If （[Status]）over（Previous（[RowNum]）） - First（[status]）over（[RowNum]））= 0，[Rank] - Max（[RowNum]）OVER（相交（[status] ，AllPrevious（[RowNum]））））并将其命名为 GroupOfTypes


4. code> If（[type]> Min（[type]）over（Intersect（[GroupOfTypes]，AllNext（[RowNum]））），T）并将其命名为标记。这是你真正关心的那一行。

结果

说明

RankReal（[status]，ties.method = first）

这完成了基本上创建一个基于伪rownumber在一段状态。你会看到它根据状态按顺序排列。这是我的方法分组数据的第一步。

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标记。

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,

• searching for the next nearest TYPE=0 for TYPE=1
• if the TYPE=1 has nearest TYPE=0, then mark it as 'T' in a new calculated column, otherwise as NULL

Data Rules:

• Status column contains{1,2} in order, the default value of the null space is same as the last nearest Status value above it.
• Type column contains only 0 and 1 randomly

output should be like this:

the solution what i have tried:

    If(([type]=1) and (first([type]) OVER (intersect(previous([type]),AllNext([status])))=0),"T",Null)

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:

• the first Type Value of the status is NULL
• Other null space in the status column can be filled as below, if it is helpful for the expression :):

解决方案

Ok, this one really tested my limits and I'm curious if it will scale. It works for your data, given where you have NULL. It took a few hours to figure out.

1. Insert a calculated column RowId() and name it RowNum
2. Insert a calculated column RankReal([status],"ties.method=first") and name it Rank
3. Insert a calculated column If((first([status]) over (Previous([RowNum])) - First([status]) over ([RowNum]))=0,[Rank] - Max([RowNum]) OVER (Intersect([status],AllPrevious([RowNum])))) and name it GroupOfTypes
4. Inert a calculated column If([type]>Min([type]) over (Intersect([GroupOfTypes],AllNext([RowNum]))),"T") and name it Marking. This is the row you really care about.

Results

EXPLANATION

RankReal([status],"ties.method=first")

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.

If((first([status]) over (Previous([RowNum])) - First([status]) over ([RowNum]))=0,[Rank] - Max([RowNum]) OVER (Intersect([status],AllPrevious([RowNum]))))

The first part of this, (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")

Lastly we evaluate if the [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.

这篇关于在Spotfire中的同一列中匹配组（表达式）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！