Parse Years and Range of Years from Strings

[johnca]

Beating my head here. I have a flat table of data and one of the fields is a string of years or range of years. The owner wants each line to be separated such that each year represented is on a single line. I have a minimum year of 1990 and max year of the current year. There are other fields associated to the year field. The strings look like these...

2018+

1996 and Older

1996 to Present

1997-2006

2008-Sept 2010

Oct 2010-Present

There are actually many variations of these like 2007-2015, 2007-2012, etc., and 2006 and Older, etc.

2018 is simple...

1996 and Older should be...

1990

1991

1992

1993

1994

1995

1996

1996 to Present should be...

1996

1997

1998

...

2018

1997-2006 should be...

1997

1998

1999

...

2006

2008-Sep 2010 should be...

2008

2009

Sep 2010

and finally,

Oct 2010-Present should be...

Oct 2010

2011

2012

...

2018

Any ideas? I've tried multiple SubField expressions but don't know how to include them in (multiple) for-next loops that I think will do the trick.

Regards,

John

[settu_periasamy · Accepted solution]

Hi John,

May be try this..

Let vStartYear=1990;

Let vEnd=Year(Today());

Temp:
LOAD * INLINE [
Source_String
2018+
1996 and Older
1996 to Present
  1997-2006
  2008-Sept 2010
Oct 2010-Present
];

FOR Each String   in FieldValueList('Source_String')

Let  vOlder         = SubStringCount('$(String)','Older');
Let vPresent      =  SubStringCount('$(String)','Present');
Let vPlus            = SubStringCount('$(String)','+');
Let vHyphen               = SubStringCount('$(String)','-');

if $(vPlus)=1 and  $(vOlder)=0  and $(vPresent)=0 and $(vHyphen)=0 THEN

T1:
LOAD        '$(String)' as String,
SubField('$(String)','+',1) as ParseYear
AutoGenerate 1;

ELSEIF $(vOlder) = 1 and  $(vPlus)=0  and $(vPresent)=0 and $(vHyphen)=0  THEN

Let vOlder_Year_End = SubField('$(String)','and',1);
Let vOlder_Year_Start = $(vStartYear);
Let vOlder_Loop = $(vOlder_Year_End) - $(vOlder_Year_Start) +1;

LOAD         '$(String)' as String,
($(vOlder_Year_Start) + RecNo()-1) as ParseYear AutoGenerate $(vOlder_Loop);

ELSEIF $(vPresent)=1 and $(vPlus)=0 and  $(vOlder)=0  and $(vHyphen)=0 THEN


Let vPresent_Year_Start = SubField('$(String)','to',1);
Let vPresent_Year_End = $(vEnd);              
Let vPresent_Loop = $(vPresent_Year_End) - $(vPresent_Year_Start) +1;

LOAD        '$(String)' as String,
($(vPresent_Year_Start) + RecNo()-1) as ParseYear AutoGenerate $(vPresent_Loop);


ELSEIF $(vHyphen)=1 and $(vPlus)=0 and  $(vOlder)=0  and $(vPresent)=0   THEN

Let vHyphen_Year_Start    = SubField('$(String)','-',1);
Let vHyphen_Year_End               = SubField('$(String)','-',-1);

if Len('$(vHyphen_Year_Start)') = 4 and Len('$(vHyphen_Year_End)') = 4 THEN

Let vHyphen_Year_Loop = $(vHyphen_Year_End) - $(vHyphen_Year_Start) +1;

LOAD        '$(String)' as String,
($(vHyphen_Year_Start) + RecNo()-1) as ParseYear AutoGenerate $(vHyphen_Year_Loop);   

ELSEIF       Len('$(vHyphen_Year_Start)') = 4 and Len('$(vHyphen_Year_End)') >4 THEN

Let  vHyphen_Year_Last = Right('$(vHyphen_Year_End)',4);

Let vHyphen_Year_Loop = $(vHyphen_Year_Last) - $(vHyphen_Year_Start) ;

LOAD        '$(String)' as String,
($(vHyphen_Year_Start) + RecNo()-1) as ParseYear AutoGenerate $(vHyphen_Year_Loop);   

LOAD        '$(String)' as String,
'$(vHyphen_Year_End)'  as ParseYear AutoGenerate 1;                                            

ENDIF

ELSEIF $(vHyphen)=1 and $(vPlus)=0 and  $(vOlder)=0  and $(vPresent)=1   THEN


Let vHyphen_Year_Start    = SubField('$(String)','-',1);
Let vHyphen_Year_End               = $(vEnd);
Let  vHyphen_Year_Start_1 = Right('$(vHyphen_Year_Start)',4);
Let vHyphen_Year_Loop = $(vHyphen_Year_End) - $(vHyphen_Year_Start_1) ;

LOAD        '$(String)' as String,
($(vHyphen_Year_Start_1) + RecNo()) as ParseYear AutoGenerate $(vHyphen_Year_Loop);  

LOAD        '$(String)' as String,
'$(vHyphen_Year_Start)' as ParseYear AutoGenerate 1;                                           

ENDIF


NEXT String

DROP Table Temp;


EXIT SCRIPT;

[johnca]

Wow, that is exactly it, Settu! Many thanks!

Now all I need to do is incorporate that with the other associated record fields and my task is done.

And, for some reason, Correct answer is not available to me, but this is the correct solution, or at least, a correct solution. Why would that option not be available? Hmm...

V/r,

John

[settu_periasamy]

Hi John,

May be you are opening this thread from your inbox. Can you check this link?

Parse Years and Range of Years from Strings

[johnca]

Yep, that was it. Thanks again!

[johnca]

Hi Settu,

Alas, I was not forthright in my initial query. There are actually numerous other year fields very similar to these but with narrower and wider inclusions. I created a new discussion so that it may be addressed separately. (You answered this one beautifully but didn't match my full dataset. Although similar, many years did not get parsed).

The new discussion is at Parse Years and Range of Years from Strings - Followup

V/r,

John