If I have startDate and endDate in my data, how can I create weeks starting from the startDate field and not the start of the year. So, basically I want to populate weeks between the two dates.
May be this?
Load *, Week(Date) as Week;
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD Min(startDate) as MinDate,
Max(endDate) as MaxDate
I would suggest the method from Anil Babu. If you want another method you could use a Cartesion join:
LEFT JOIN (ExampleTable) LOAD
RESIDENT [Master Calendar];
WEEK(Date) AS Week
WHERE DATE(Date ) >= DATE([StartDate]) AND DATE(Date) <= DATE([EndDate])
DROP TABLE ExampleTable;
May be this?
Load * From Table;
LOAD Week(Final_Date) As Week;
Load Date(MinDate + IterNo() -1 ) AS Final_Date While Date(MinDate + IterNo() - 1) <= MaxDate;
Min(startDate) AS MinDate,
Max(endDate) AS MaxDate
By resident load you can try this way
let startDate = Num(MakeDate(2015,1,1));
let endDate = Num(MakeDate(2017,12,30));
Date($(startDate) + IterNo() -1) as Date
While Date($(startDate) + IterNo() -1) <= $(endDate);
Week(Date) as Week
DROP Table tmpCalendar;
Thanks for this, however my challenge is to create week intervals based off of a period of 84 days. So, essentially my data will only have week 1 to week 12 given the startDate and endDate.
Okay, Can you make sample and explain exact result set you want to see?