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

[martyn_birzys]

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-Week	Weekday
2016-9	Fri
2016-9	Mon
2016-9	Sat
2016-9	Sun
2016-9	Thu
2016-9	Tue
2016-9	Wed
2016-10	Fri
2016-10	Mon
2016-10	Sat
2016-10	Sun
2016-10	Thu
2016-10	Tue
2016-10	Wed

[sunny_talwar]

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

[Anonymous]

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]

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

[Anonymous]

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]

=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 · Accepted solution]

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 ];