topic Re: Loading weeks as mm/dd/yyyy in QlikView

Loading weeks as mm/dd/yyyy

Thu, 26 May 2016 10:37:18 GMT

Hi I have a field called SALES_WEEK with the following values:

2015-01

2015-02

2015-03

What I need to do is load these values in a standard date format ie mm/dd/yyyy using the first day of each week. So expected output would be :

1/1/2015

5/1/2015

12/1/2015

19/1/2015

Any assistance would be appreciated

Re: Loading weeks as mm/dd/yyyy

sunny_talwar — Thu, 26 May 2016 10:45:13 GMT

May be something along these lines:

Table:

LOAD * Inline [

SALES_WEEK

2015-01

2015-02

2015-03

];

Left Join (Table)

LOAD Date,

Year(Date) & '-' & Num(Week, '00') as SALES_WEEK

Where Week <> Previous(Week);

LOAD Date(MakeDate(2015, 1, 1) + RecNo() - 1) as Date,

Week(MakeDate(2015, 1, 1) + RecNo() - 1) as Week

AutoGenerate 60;

Re: Loading weeks as mm/dd/yyyy

Digvijay_Singh — Thu, 26 May 2016 10:45:52 GMT

Try Date(makeweekdate(subfield(SALES_WEEK,'-',1),Subfield(SALES_WEEK,'-',2)),'MM/DD/YYYY')

Re: Loading weeks as mm/dd/yyyy

sunny_talwar — Thu, 26 May 2016 10:47:58 GMT

Much better than my approach.

Re: Loading weeks as mm/dd/yyyy

Anonymous — Thu, 26 May 2016 10:51:52 GMT

MakeWeekDate(YYYY [ , WW [ , D ] ] )

Returns a date calculated from the year YYYY, the week WW and the day-of-week D.

If no day-of-week is stated, 0 (Monday) is assumed.

Examples:

makeweekdate(1999,6,6) returns 1999-02-14

makeweekdate(1999,6) returns 1999-02-08

Re: Loading weeks as mm/dd/yyyy

Thu, 26 May 2016 11:01:12 GMT

Thanks for your help Sunny - went with Digvijay's approach as slightly simpler.

Re: Loading weeks as mm/dd/yyyy

Thu, 26 May 2016 11:01:28 GMT

Works perfectly - thank you

Re: Loading weeks as mm/dd/yyyy

Digvijay_Singh — Thu, 26 May 2016 11:02:26 GMT

You are welcome!

Re: Loading weeks as mm/dd/yyyy

sunny_talwar — Thu, 26 May 2016 11:06:49 GMT

I agree, it is indeed the way to do it. Mine was just a work around