QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Convert week number and year to a standard date

I'm struggling with a date format that I would like to convert...

The format that I have from the data source is like this: 05WO05

05 = week number

WO = the abbreviation for "week" in german

05 = the year, i.e. 2005 (data goes back to 1993!)

My goal is a format that will enable to me to have the data sorted by month and calendar year.

Any idea?

Many thanks for your help!

Regards

Günter

1 Solution

Accepted Solutions
MVP

Re: Convert week number and year to a standard date

Hi,

Try MakeWeekDate() function

=MakeWeekDate(Year, Week)

Example:

=MakeWeekDate(2014, 2)

Data:

LOAD

*,

MakeWeekDate(If(Num(Right('05WO93', 2)) >= 93, 1900, 2000) + Num(Right('05WO93', 2)),

Num(Left('05WO93', 2)))

AS Date

FROM DataSource;

Regards,

Jagan.

7 Replies
MVP

Re: Convert week number and year to a standard date

Something like this

Month

=MONTH(Date(MakeDate(NUM(Right('05WO05',2)+2000))+(WeekStart(Left('05WO05',2))-1)*7))

Year

=Year(Date(MakeDate(NUM(Right('05WO05',2)+2000))+(WeekStart(Left('05WO05',2))-1)*7))

Date

=Date(MakeDate(NUM(Right('05WO05',2)+2000))+(WeekStart(Left('05WO05',2))-1)*7)

MVP

Re: Convert week number and year to a standard date

Use MakeWeekDate().

Valued Contributor II

Re: Convert week number and year to a standard date

Hi Manish,

Your expression will not work for years before 2000.

Valued Contributor II

Re: Convert week number and year to a standard date

Below is the script to generate weekDt, Month & Year for the given data. Assumption is data starts from 1950 onwards.

Load *, Month(WeekDt),Year(WeekDt);

Load *, MakeWeekDate(Yr,WkNo) AS WeekDt;

Load *,

Left(Dt,2) AS WkNo,

if(right(Dt,2)>0 AND right(Dt,2)<15,(2000+right(Dt,2)),

if(right(Dt,2)>50 AND right(Dt,2)<99,(1900+right(Dt,2)))) AS Yr;

LOAD * INLINE [

Dt

05WO05

35WO04

07WO76

12WO84

];

MVP

Re: Convert week number and year to a standard date

Hi,

Try MakeWeekDate() function

=MakeWeekDate(Year, Week)

Example:

=MakeWeekDate(2014, 2)

Regards,

Jagan.

MVP

Re: Convert week number and year to a standard date

Hi,

Try MakeWeekDate() function

=MakeWeekDate(Year, Week)

Example:

=MakeWeekDate(2014, 2)

Data:

LOAD

*,

MakeWeekDate(If(Num(Right('05WO93', 2)) >= 93, 1900, 2000) + Num(Right('05WO93', 2)),

Num(Left('05WO93', 2)))

AS Date

FROM DataSource;

Regards,

Jagan.

Not applicable

Re: Convert week number and year to a standard date

Jagan,

thank you very much - the MakeWeekDate approach was perfectly right. The only thing that needed to be changed was the 93 to 92 as I had some dates ranging back to 1992, which I did not notice myself. I was able to create now a complete date, a month+year and a year date field.

MakeWeekDate(If(Num(Right(Lieferzusage, 2)) >= 92, 1900, 2000) + Num(Right(Lieferzusage, 2)),

Num(Left(Lieferzusage, 2)))

as Lieferdatum,

MonthName(MakeWeekDate(If(Num(Right(Lieferzusage, 2)) >= 92, 1900, 2000) + Num(Right(Lieferzusage, 2)),

Num(Left(Lieferzusage, 2)))) as Liefermonat,

YearName(MakeWeekDate(If(Num(Right(Lieferzusage, 2)) >= 92, 1900, 2000) + Num(Right(Lieferzusage, 2)),

Num(Left(Lieferzusage, 2)))) as Lieferjahr,

Thank you and all the other guys who responded so unbelievable fast.

Regards

Günter