Parse Years and Range of Years from Strings - Followup

[johnca]

Many thanks to Settu for accurately parsing out the years from the strings I provided in the initial thread. This is a followup to that and I admit I missed a few years to parse from. I did because I thought they were all similar and they are, but the results will be different. Each of them are associated to records with several other fields.

This is what I requested help on initially;

2018+

1996 and Older

1996 to Present

1997-2006

2008-Sept 2010

Oct 2010-Present

Parse Years and Range of Years from Strings

Here is new list of years and the expected outcomes are the same as those described in the initial thread (a year for each included value), linked to above. So, 1996 and Older would be each year from 1996 down to 1990. 2006 would be each year from 2006 down to 1990, etc.

2018+

1996 and Older

1996 to Present

1997-2006

1997-2013

2006 and Older

2007-2012

2007-2013

2007-2015

2007 and Older

2008-Sept 2010

2008 - 2010

2008 and Older

2009 and Older

2010 - Present

2011 - Present

2013-2017

2014-Present

2014 to Present

2016-Present

2016 - Present

2017-Present

Oct 2010-Present

[johnca]

I tried adding another if statement but it kept bombing on me so I looked at the data again. If the differences are only a hyphen vs a space-hyphen-space I was able to get the owner to change his file such that the spaces were removed in the Year to Year values. So, '2008 - 2010' became '2008-2010', etc. and now it works.

I will continue with a more robust If Then ElseIf for the original values but one of the things a developer does is cleanse data too.

Again, thanks to Settu for providing the original fix.

[swuehl · Accepted solution]

Maybe like this

INPUT:

LOAD * INLINE [

String

2018+

1996 and Older

1996 to Present

1997-2006

1997-2013

2006 and Older

2007-2012

2007-2013

2007-2015

2007 and Older

2008-Sept 2010

2008 - 2010

2008 and Older

2009 and Older

2010 - Present

2011 - Present

2013-2017

2014-Present

2014 to Present

2016-Present

2016 - Present

2017-Present

Oct 2010-Present

];

MAP_DELIM:

MAPPING

LOAD In, '|' as Out INLINE [

In

-

and

to

+

];

MAP_LIMITS:

MAPPING

LOAD In, Evaluate(Out) as Out INLINE [

In, Out

+, Year(Today())

Present, Year(Today())

Older, 1990

];

TMP:

LOAD *,

keepchar(ApplyMap('MAP_LIMITS',trim(left)),'0123456789') as left_limits,

keepchar(ApplyMap('MAP_LIMITS',trim(right)),'0123456789') as right_limits;

LOAD *,

Subfield(Mapped_delim,'|',1) as left,

Subfield(Mapped_delim,'|',-1) as right;

LOAD String,

  MapSubString('MAP_DELIM',String) as Mapped_delim

Resident INPUT;

RESULT:

LOAD String, Rangemin(left_limits,right_limits)+iterno()-1 as Year

Resident TMP

While Rangemin(left_limits,right_limits)+iterno()-1 <= Rangemax(left_limits,right_limits);

String	Year	1990	1991	1992	1993	1994	1995	1996	1997	1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018
2018+		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1
1996 and Older		1	1	1	1	1	1	1	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
1996 to Present		-	-	-	-	-	-	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
1997-2006		-	-	-	-	-	-	-	1	1	1	1	1	1	1	1	1	1	-	-	-	-	-	-	-	-	-	-	-	-
1997-2013		-	-	-	-	-	-	-	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	-	-	-	-	-
2006 and Older		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	-	-	-	-	-	-	-	-	-	-	-	-
2007-2012		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	1	1	1	-	-	-	-	-	-
2007-2013		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	1	1	1	1	-	-	-	-	-
2007-2015		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	1	1	1	1	1	1	-	-	-
2007 and Older		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	-	-	-	-	-	-	-	-	-	-	-
2008-Sept 2010		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	-	-	-	-	-	-	-	-
2008 - 2010		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	-	-	-	-	-	-	-	-
2008 and Older		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	-	-	-	-	-	-	-	-	-	-
2009 and Older		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	-	-	-	-	-	-	-	-	-
2010 - Present		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	1	1	1	1	1	1
2011 - Present		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	1	1	1	1	1
2013-2017		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	1	1	-
2014-Present		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	1	1
2014 to Present		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	1	1
2016-Present		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1
2016 - Present		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1
2017-Present		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1
Oct 2010-Present		-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1	1	1	1	1	1	1	1	1

[johnca]

Wow, Stephan, this too works, and actually a bit better and easier to maintain I think. I was able to apply this one to the existing document as-is and didn't need to clean an data. 🙂

Many thanks for the input...I will probably use this one in the final dashboard.

V/r,

John