Loading weeks as mm/dd/yyyy

[#1]

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

[sunny_talwar]

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;

[Digvijay_Singh · Accepted solution]

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

[sunny_talwar]

Much better than my approach.

[Anonymous]

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

[#6]

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

[#7]

Works perfectly - thank you

[Digvijay_Singh]

You are welcome!

[sunny_talwar]

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