Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 ];
What dates do they correspond to? Is 2016-9 Fri = Feb 26th? and 2016-9 Mon = Feb 22nd?
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.
2016-9 Fri is today 04/03/2016, 2016-9 Mon is 29/02/2016
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#()
Bill isn't the first column Year-Week, wouldn't we need something which is estimating Week instead of M ('YYYY-M')
=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.
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 ];