Compare with values in other columns within an Over row as a calculated column.

[Kibong Kang]

I have data with product numbers, process numbers, X coordinates, and Y coordinates.

The process numbers are text values such as 1000, 2000, and 3000, and each coordinate has X and Y values.

If i want to calculate the number of rows that meet the following criteria or concatenate them, where the process number is 2000 and within the range of +-20 on the X-axis and +-20 on the Y-axis, while the process number is 1000

if([Process Number]<>'2000',Null, Sum(if([Process Number]='1000' and [X]<(?+20) AND [X]>(?+20) AND [Y]<(?+20) AND [Y]>(?+20) ,1,0) OVER ([Product Number])

However, in this formula, the '?' part should also be applied based on the product number of the overlying row, which is causing the desired values not to appear.

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

Sorry, the computer with Spotfire installed is my company computer, but I couldn't post an example because it was difficult to upload large text due to security.

please test it with the data.

The 'Moving Count' column is the value I want to get.

Among the rows with the value of "Process Number" calculating only 2000 and having the same "Product Number" of "Process Number" 1000 are candidates, and I would like to get the count of rows with the values of X and Y +-2 among them.

Product Number,Process Number,X,Y,Margin as Property,Maching Count,Comment

1,1000,9,16,2,Null,Only calculate at 2000

1,2000,6,12,2,0,Calculate only x and y of 1000 +-2

1,2000,2,19,2,0,Calculate only x and y of 1000 +-2

1,3000,2,12,2,Null,Only calculate at 2000

1,1000,7,16,2,Null,Only calculate at 2000

1,2000,6,2,2,0,Calculate only x and y of 1000 +-2

2,1000,2,7,2,Null,Only calculate at 2000

2,2000,1,4,2,0,Calculate only x and y of 1000 +-2

2,2000,6,5,2,0,Calculate only x and y of 1000 +-2

2,2000,19,19,2,0,Calculate only x and y of 1000 +-2

2,2000,2,8,2,1,Calculate only x and y of 1000 +-2

2,1000,7,13,2,Null,Only calculate at 2000

2,3000,5,7,2,Null,Only calculate at 2000

3,3000,8,12,2,Null,Only calculate at 2000

3,1000,14,3,2,Null,Only calculate at 2000

3,1000,9,8,2,Null,Only calculate at 2000

3,1000,4,6,2,Null,Only calculate at 2000

3,2000,10,10,2,1,Calculate only x and y of 1000 +-2

3,2000,12,4,2,1,Calculate only x and y of 1000 +-2

3,2000,11,14,2,0,Calculate only x and y of 1000 +-2

[Gaia Paolini]

Can you clarify your question?

• what does it mean for the process number to be 2000 and at the same time the process number being 1000 and within the range +-20 of X and Y.
• what is "?" in this formula?
• what do you mean by overlying row?
• what do you mean by concatenating rows? Is the desired result a new data table with fewer rows? In that case, a calculated column would not do it

The best way would be to provide a sample dataset with an example of the result you wish to achieve.

[Kibong Kang]

Sorry, the computer with Spotfire installed is my company computer, but I couldn't post an example because it was difficult to upload large text due to security.

If you don't mind, please test it with the data.

The 'Moving Count' column is the value I want to get.

Among the rows with the value of "Process Number" calculating only 2000 and having the same "Product Number" of "Process Number" 1000 are candidates, and I would like to get the count of rows with the values of X and Y +-2 among them.

Product Number,Process Number,X,Y,Margin as Property,Maching Count,Comment

1,1000,9,16,2,Null,Only calculate at 2000

1,2000,6,12,2,0,Calculate only x and y of 1000 +-2

1,2000,2,19,2,0,Calculate only x and y of 1000 +-2

1,3000,2,12,2,Null,Only calculate at 2000

1,1000,7,16,2,Null,Only calculate at 2000

1,2000,6,2,2,0,Calculate only x and y of 1000 +-2

2,1000,2,7,2,Null,Only calculate at 2000

2,2000,1,4,2,0,Calculate only x and y of 1000 +-2

2,2000,6,5,2,0,Calculate only x and y of 1000 +-2

2,2000,19,19,2,0,Calculate only x and y of 1000 +-2

2,2000,2,8,2,1,Calculate only x and y of 1000 +-2

2,1000,7,13,2,Null,Only calculate at 2000

2,3000,5,7,2,Null,Only calculate at 2000

3,3000,8,12,2,Null,Only calculate at 2000

3,1000,14,3,2,Null,Only calculate at 2000

3,1000,9,8,2,Null,Only calculate at 2000

3,1000,4,6,2,Null,Only calculate at 2000

3,2000,10,10,2,1,Calculate only x and y of 1000 +-2

3,2000,12,4,2,1,Calculate only x and y of 1000 +-2

3,2000,11,14,2,0,Calculate only x and y of 1000 +-2

[Gaia Paolini]

Thanks.

There are multiple lines with Process Number=1000 followed by multiple lines where Process Number = 2000.

How does one relate the 2000 to the corresponding 1000?

Does the calculation try to test if X and Y are in the same numeric range?

[Kibong Kang]

First, Please understand that it is difficult to communicate because my English is not good. 🙏🙏

​

How does one relate the 2000 to the corresponding 1000?

> Same 'Product Number'

Among the rows with the same 'Product Number' and the objects with 'Process Number' of 1000, I would like to count the rows in the +-2 range compared to the X and Y of the current row.

​

Does the calculation try to test if X and Y are in the same numeric range?

> Yes, I want to get the value of 'Maching Count' by adding calculate column.

The actual data is in the Product Number, Process Number, X, and Y columns, and 'Margin as Property' is the property value, and the value of 'Maching Count' is the value I want to obtain.

[Gaia Paolini]

Thank you. I understand the question now. The structure of the data is not easy for this, as for each "2000" row, one needs to look around for "1000" rows.

Are you ok with using a data function, should it not be possible with a calculated column? If yes, would you prefer R or Python?

[Kibong Kang]

I'd like to make it as calculated column as possible.

As a beginner in Spotfire, I would also like to know about Data Functions.

Among Python and R, Python is familiar.

If you have time, please tell me various ways

Thank you.

[Gaia Paolini]

I could only achieve it by calculating a column by using a TERR (basically R) expression function.

Unfortunately there is no Python version for expression functions.

What happens is you create this expression function and it becomes available to you for calculating a new column.

This video might help:

I only tested it with the sample data you provided.

The syntax to call it would be:

TERR_CalculateMatchingCount([Product Number],[Process Number],[X],[Y])

You use this formula in a custom expression editor to calculate your new column.

You need to load the R library data.table into Spotfire to use it.

To do that: 

go to top menu > Tools > TERR Tools 

choose tab: Package Management

Load the CRAN Package Repository

into Available Packages type 'data.table'

when it appears in the window below, select it and click Install.

then Close and exit.

To create a TERR Expression Function:

go to top menu > Data > Data Function Properties

choose tab Expression Functions

click on New...

give it the Name TERR_CalculateMatchingCount

Function type: Column function

Return type: Integer

Category: Statistical functions

Copy and paste the attached script.

The script has 4 inputs (input1,input2,input3,input4) which will be the entire column specified at the start.

It has one output, which is the new column.

There are some hard-coded values there: 1000 and 2000 as process number values, and 2 as the maximum distance between coordinates.

For each product number, this maximum distance is calculated across the rows with the selected product numbers.

Then only the distances between 1000 and 2000 are kept, when less than or equal to 2.

These are accumulated for each product number, then the counts of rows that are in the desired range are summed up and merged back to the original dataset.

At this point, the count column is extracted and returned.

It needs merging as it needs to be returned in exactly the same order that the data came in.