Hi,
I am trying to create a new column (in my file "Required values") which can have only binary values - 0/1 or Start/End. Its values depends upon a another column (in my file "Number of Occurrence") which has repeated pattern based on other two columns (in my file - "ID" and "Sub_set"). Too much wording, please see the attached image and data file. I tried few formulas in new columns and few for loops as well. But I am not able to get both Start and End values. I am able to get only Start value.
Any help ?
Hi @Hari ,
Can you provide some basic logic example of how the "required values" column depends on the "number of occurrence", "sub-set", and "ID"? Based on your description, it's unclear how these columns lead to "required values" having either a 0 or 1 output.
For example, you have both row 1 and row 13 as 0, while rows 12 and 25 are both 1. It's unclear from the structure of the data table why these values should be set this way. If you can provide a little more logic explanation, it would help in generating the right kind of of column formula.
For example,
If( :"data"n ==0, :"required values"n = 0,
:"data"n>=0.24, :"required values"n=1,
.
);
In the above example, the logic is that if the "data" column is equal to 0, then the entry in the "required values" column is equal to 0; else if the value of "data" is greater than or equal to 0.24, then the entry for "required values" is equal to 1. This would satisfy the elements for required values, but might also include some other entries that aren't the last element of the sub-set type.
I know this isn't exactly what you're looking for to apply to the whole column, but getting a better idea of the logic that you need to apply will help in creating the right type of column formula that will apply to the whole column.
DS
Here is a formula that is one way of calculating what you need
If(
Row() == 1, 0,
:Number of Occurance != Lag( :Number of Occurance ), 0,
:Number of Occurance != :Number of Occurance[Row() + 1], 1,
Row() == N Rows( Current Data Table() ), 1,
.
)
