topic Re: Convert week number and year to a standard date in QlikView

Convert week number and year to a standard date

Wed, 18 Jun 2014 10:20:17 GMT

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

Re: Convert week number and year to a standard date

MK_QSL — Wed, 18 Jun 2014 10:35:14 GMT

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)

Re: Convert week number and year to a standard date

tresB — Wed, 18 Jun 2014 10:37:02 GMT

Use MakeWeekDate().

Re: Convert week number and year to a standard date

manojkulkarni — Wed, 18 Jun 2014 10:44:51 GMT

Hi Manish,

Your expression will not work for years before 2000.

Re: Convert week number and year to a standard date

manojkulkarni — Wed, 18 Jun 2014 10:56:23 GMT

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 [

05WO05

35WO04

07WO76

12WO84

];

Re: Convert week number and year to a standard date

jagan — Wed, 18 Jun 2014 10:59:56 GMT

Hi,

Try MakeWeekDate() function

=MakeWeekDate(Year, Week)

Example:

=MakeWeekDate(2014, 2)

Regards,

Jagan.

Re: Convert week number and year to a standard date

jagan — Wed, 18 Jun 2014 11:10:09 GMT

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.

Re: Convert week number and year to a standard date

Wed, 18 Jun 2014 14:01:46 GMT

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