Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
martyn_birzys
Creator
Creator

Convert year-week and weekday fields to date dd/mm/yyyy

Hi, my data comes across in two fields, one contains year-week and the other one weekday name. Any suggestions how to convert this to day/month/year most efficiently?

 

Year-WeekWeekday
2016-9Fri
2016-9Mon
2016-9Sat
2016-9Sun
2016-9Thu
2016-9Tue
2016-9Wed
2016-10Fri
2016-10Mon
2016-10Sat
2016-10Sun
2016-10Thu
2016-10Tue
2016-10Wed
1 Solution

Accepted Solutions
martyn_birzys
Creator
Creator
Author

This has worked for me: MakeWeekDate(num(SubField([Year-Week],'-',1)),num(SubField([Year-Week],'-',2)),num(Weekday)), where Weekday was transformed into Dual using the script below. Struggled with that a bit, the transformation to dual() must happen at the last step of data transformation, otherwise it seems to revert back to num().

Weekdays:

load * inline as Weekay

[ Wkstring,Wknumrep

Mon,0

Tue,1

Wed,2

Thu,3

Fri,4

Sat,5

Sun,6 ];

View solution in original post

7 Replies
sunny_talwar

What dates do they correspond to? Is 2016-9 Fri = Feb 26th? and 2016-9 Mon = Feb 22nd?

Anonymous
Not applicable

I thought I would have look at what this returned :

     =date(today(),'YYYY-M WWWW')

And was most surprised to see it return :

     2016-3 viernes

The viernes is Spanish for Friday which is most surprising as neither me or my laptop have ever been to a spanish speaking country.  I did not even know what meant viernes without a web search.

I was going to knock up a date#() expression, but I am flummoxed by the above.

martyn_birzys
Creator
Creator
Author

2016-9 Fri is today 04/03/2016, 2016-9 Mon is 29/02/2016

Anonymous
Not applicable

Ooops.  I did it in a qvw that originated from Spain, in a new qvw it returns 2016-3 Friday

And =date(today(),'YYYY-M WWW') returns 2016-3 Fri

So you should be able do your conversion with a suitable date#()

sunny_talwar

Bill‌ isn't the first column Year-Week, wouldn't we need something which is estimating Week instead of M ('YYYY-M')

Anonymous
Not applicable

=WeekName(today()) returns 2016/09

But I don't know a function to go from week number to date, without a loop to generate a table of week numbers and week start dates and using that for an ApplyMap(), and it is a bit too late in the UK to start on that.

martyn_birzys
Creator
Creator
Author

This has worked for me: MakeWeekDate(num(SubField([Year-Week],'-',1)),num(SubField([Year-Week],'-',2)),num(Weekday)), where Weekday was transformed into Dual using the script below. Struggled with that a bit, the transformation to dual() must happen at the last step of data transformation, otherwise it seems to revert back to num().

Weekdays:

load * inline as Weekay

[ Wkstring,Wknumrep

Mon,0

Tue,1

Wed,2

Thu,3

Fri,4

Sat,5

Sun,6 ];