Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Parsing string into date

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

1 Solution

Accepted Solutions
Partner - Champion III

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

``````// Modify the string for easier parsing
Step1:
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:
RecNo,
SubField(Parse1, ' ') as Clause1
Resident Step1;

// Unpack the quantities
Step3:
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:
RecNo,
+ Sum(Weeks) * 7
+ Sum(Days)) as LastExtractDate
Resident Step3
Group By  ExtractDate, RecNo;

// Clean up
DROP Tables Step1, Step2, Step3;
``````
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
Creator III

[data]:
[
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
Replace(Replace(LastAccess, 's', ''),' ', '') as LastAccess
Resident data;

drop tables data;

Temp2:
NoConcatenate
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;
left(removemonth,index(removemonth,'week')-1) as week,
Right(removemonth,len(removemonth) - if(index(removemonth,'week')>0, index(removemonth,'week')+3,0)) as removeweek;
left(removeyear,index(removeyear,'month')-1) as month,
Right(removeyear,len(removeyear) - if(index(removeyear,'month')>0, index(removeyear,'month')+4,0)) as removemonth;
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;

Partner - Champion III

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

``````// Modify the string for easier parsing
Step1:
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:
RecNo,
SubField(Parse1, ' ') as Clause1
Resident Step1;

// Unpack the quantities
Step3:
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:
RecNo,
+ Sum(Weeks) * 7
+ Sum(Days)) as LastExtractDate
Resident Step3
Group By  ExtractDate, RecNo;

// Clean up
DROP Tables Step1, Step2, Step3;
``````
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Specialist II

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

Contributor III
Author

the solutions worked perfectly! thank you!