topic Re: Convert year-week and weekday fields to date dd/mm/yyyy in QlikView

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

martyn_birzys — Fri, 04 Mar 2016 19:13:53 GMT

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

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

sunny_talwar — Fri, 04 Mar 2016 19:17:44 GMT

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

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

Anonymous — Fri, 04 Mar 2016 19:23:22 GMT

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.

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

martyn_birzys — Fri, 04 Mar 2016 19:24:26 GMT

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

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

Anonymous — Fri, 04 Mar 2016 19:29:49 GMT

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#()

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

sunny_talwar — Fri, 04 Mar 2016 19:41:46 GMT

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

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

Anonymous — Fri, 04 Mar 2016 20:03:11 GMT

=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.

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

martyn_birzys — Fri, 04 Mar 2016 23:07:05 GMT

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