Parsing string into date

[adrianfer]

Good day! I have a table that I am trying to load:

Extract Date	Last Access
5/02/2020	3 years 6 months
5/02/2020	3 weeks 3 days
5/02/2020	1 month 4 days
5/02/2020	5 days

How do I parse the 'Last Access' column in such a way that it is going to return the actual 'Last Access Date' based on the 'Extract Date'? For example, the last row would return 31/01/2020

[thi_pham]

[data]:
Load * Inline
[
ExtractDate,LastAccess
5/2/2020, '3 years 6 months'
5/2/2020, '3 weeks 3 days'
5/2/2020, '1 month 4 days'
5/2/2020, '5 days'
];

[Temp1]:
NoConcatenate
Load Date#(ExtractDate,'M/D/YYYY') as ExtractDate,
Replace(Replace(LastAccess, 's', ''),' ', '') as LastAccess
Resident data;

drop tables data;

Temp2:
NoConcatenate
Load ExtractDate, AddYears(AddMonths(Date(ExtractDate-if(isnull(day),0,day)-7*week),-month),-year) as StartDate;
Load ExtractDate,
if(isnull(year),0,year) as year,
if(isnull(month),0,month) as month,
if(isnull(week),0,week) as week,
LastAccess,
left(removeweek,index(removeweek,'day')-1) as day;
Load ExtractDate, year, month, LastAccess,
left(removemonth,index(removemonth,'week')-1) as week,
Right(removemonth,len(removemonth) - if(index(removemonth,'week')>0, index(removemonth,'week')+3,0)) as removeweek;
Load ExtractDate, year, LastAccess,
left(removeyear,index(removeyear,'month')-1) as month,
Right(removeyear,len(removeyear) - if(index(removeyear,'month')>0, index(removeyear,'month')+4,0)) as removemonth;
Load ExtractDate, LastAccess,
left(LastAccess,index(LastAccess,'year')-1) as year,
Right(LastAccess,len(LastAccess) - if(index(LastAccess,'year')>0, index(LastAccess,'year')+3,0)) as removeyear
Resident Temp1;

drop table Temp1;

[jonathandienst · Accepted solution]

Here's another way. Deliberately not using preceding load to make the logic easier to understand.

// Modify the string for easier parsing
Step1:
LOAD 
	ExtractDate,
	Replace(
		Replace(
			Replace(
				Replace(PurgeChar(LastAccess, 's'), ' year', '~Y'),
			' month', '~M'),
		' week', '~W'),
	' day', '~D') as Parse1,
	RecNo() as RecNo
FROM ....

// Split into tokens like 1~Y, 6~M, 3~W, 4~D
Step2:
LOAD ExtractDate,
	RecNo,
	SubField(Parse1, ' ') as Clause1
Resident Step1;

// Unpack the quantities 
Step3:
LOAD ExtractDate,
	RecNo,
	If(SubField(Clause1, '~', 2) = 'Y', SubField(Clause1, '~', 1) as Years,
	If(SubField(Clause1, '~', 2) = 'M', SubField(Clause1, '~', 1) as Months,
	If(SubField(Clause1, '~', 2) = 'W', SubField(Clause1, '~', 1) as Weeks,
	If(SubField(Clause1, '~', 2) = 'D', SubField(Clause1, '~', 1) as Days,
Resident Step2;

// Group and calculate the date
Final:
LOAD ExtractDate,
	RecNo,	
	Date(AddMonths(AddYears(ExtractDate, Sum(Years)), Sum(Months)) 
	+ Sum(Weeks) * 7 
	+ Sum(Days)) as LastExtractDate
Resident Step3
Group By  ExtractDate, RecNo;

// Clean up
DROP Tables Step1, Step2, Step3;
	

[anushree1]

The solution seems very good , but i think null must be handled at week, month and year level as well

[adrianfer]

the solutions worked perfectly! thank you!