Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
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?

bill_markham
Champion III
Champion III

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

bill_markham
Champion III
Champion III

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')

bill_markham
Champion III
Champion III

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