November 10, 2011 at 1:50 am
Firstly apologies for the poor description of the issue in the title, I am finding it incredibly difficult to succinctly describe the issue which is making searching for help on the subject extremely difficult.
I hope that the following simplified example will be useful in explaining the issue.
I have a table of data that looks like:
idDistanceToggle
1100
2200
3301
4400
5500
6600
7700
8800
9900
101001
111100
121200
131300
141400
151500
161600
171701
181800
191900
202000
The toggle column is the one I need to 'transform' so as to give the following output (the additional 'Trace' column):
idDistanceToggleTrace
11000
22000
33011
44001
55001
66001
77001
88001
99001
1010010
1111000
1212000
1313000
1414000
1515000
1616000
1717011
1818001
1919001
2020001
In this case the Trace output is 0 for all of the values in order until their is a toggle value of 1, then the Trace column needs to flip value, it continues as a 1 until the next toggle = 1 and then flips value.
In the real data there are a number of subgroups that would need to be taken into account - let us say there is a 'DataSet' column that is the number representing the dataset in the initial table so that I can create the toggle column for each of the datasets in isolation. I am not sure if this is a good enough explanation, I can certainly try to clarify if it is not clear.
If anyone can offer me any advice or even search terms that might be helpful then I would appreciate it.
November 10, 2011 at 2:01 am
You could try searching for "gaps and islands" - this looks like a similar principle to that.
John
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply