topic Data preparation- substract column data by other column in Discussions

Data preparation- substract column data by other column

Ohad_s — Sun, 09 Jun 2024 07:39:36 GMT

Hi,

I have a table with different scan results.

I have 180 scans with 1000 results per scan.

results are at the Z column.

I want to create a new column with relative scan results, meaning the results of scan 2 - the results of scan 1, results of scan 3 - the results of scan 2, and so on.

subtraction of results must be by Location number, 1-1, 2-2, 3-3, and so on.

I've tried to transpose the data but got lost in the way.

I would appreciate any help.

Re: Data preparation- substract column data by other column

jthi — Sun, 09 Jun 2024 08:44:06 GMT

You could first split your data

Perform the calculations in the split data, stack it and then join/update it back to your original data.

Other option is to sort your data by Location# (not necessary for my formula, but easier to very if the calculation is correct) and then create a formula to calculate the differences

:Z - (Col Moving Average(:Z, 1, 1, 0, :"Location #"n) * 2 - :Z);

Re: Data preparation- substract column data by other column

Ohad_s — Sun, 09 Jun 2024 09:53:49 GMT

Thanks for the quick replay,

this works great.

can you elaborate on the formula a bit?

what are the parameters you put in?

Re: Data preparation- substract column data by other column

jthi — Sun, 09 Jun 2024 10:08:21 GMT

Col Moving Average(name, options, <By var, ...>) should explain the parameters.

The idea is to use Col Moving Average to calculate the previous value (this is "easy" method to take groups into account). You can do this type of calculations using Dif() or Lag() but they would require additional checks for group changes (simple If is usually enough for that).

Re: Data preparation- substract column data by other column

txnelson — Sun, 09 Jun 2024 11:57:07 GMT

The Scripting Index, available under the Help pull down menu has both the definition of the function and of the parameters, along with examples.