Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
adrianfer
Contributor III
Contributor III

Parsing string into date

 

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

Extract DateLast Access
5/02/20203 years 6 months
5/02/20203 weeks 3 days
5/02/20201 month 4 days
5/02/20205 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
jonathandienst
Partner - Champion III
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:
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;
	
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
thi_pham
Creator III
Creator III


[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
Partner - Champion III
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:
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;
	
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
anushree1
Specialist II
Specialist II

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

adrianfer
Contributor III
Contributor III
Author

the solutions worked perfectly! thank you!