Hi @N904808 , I must confess that when I looked at this problem originally, it was one of those times when I thought that the best thing to do was “start from somewhere else”
Now your data is problematic in that it is inconsistent with mix of months where months in subsequent years only contain (#1) if the month name has already been used, and additionally contains two entries for the same “month name”, which use different abbreviations “Sep” and “Sept”.
So, I thought that the best thing to do initially is to “normalise” the names. Since the (#1) and the additional “t” in September don’t actually provide any useful information, let’s start by turning all months into their 3 letter abbreviations. We can do this using a String Manipulation:
This then gives us the following:
Now we have something consistent to work with. Ok, let’s see if we can derive a month number for each month:
We can do this using String Manipulation too. If we find the Month in the following String:
using the indexOf function, which gives us a value in the range 0 to 33, then add 3 to that thereby giving a value in the range 3 to 36, we can then divide that result by 3 to give us a value in the required range of 1 to 12:
At this point, I was wondering where to go from here, so I wondered if we could look at the current
row index +1 (so we have “proper” row numbers beginning at 1) and see if that could tell us anything:
Ah… look at what happens when we get past December. The month number for Jan is 1 again, and the PositionIndex is 8. So what? Well, what if the current year’s months always have a monthnumber that is equal to or greater than the PositionIndex, but next year’s months don’t?
Let’s test that hypothesis. What if our data began at January instead of June?
So far so good. At the new year, the month number for Jan in Row12 is 1 when the position index is 13
What if our current year starts at December?
The theory still holds. As the Jan in the second row has a month number of 1 which is lower than the position index.
So, we can now determine the year based on whether the month number is lower than the position index.
We could use a rule engine to determine the year based on a simple rule:
We could then use a String Manipulation to join the Month and Year columns
and after some tidy up work to remove unwanted columns, get the required result:
Alternatively, all of that logic can be done in a single String Manipulation call:
Month names and adding year information.knwf (195.4 KB)