How to generate weeks between two dates?

[Anonymous]

Hi community,

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.

Thanks

[Anil_Babu_Samineni]

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

AutoGenerate 1;

[avkeep01]

Hi Laleh,

I would suggest the method from Anil Babu. If you want another method you could use a Cartesion join:

Try

ExampleTable:

LOAD

[StartDate],

[EndDate]

FROM [source];

LEFT JOIN (ExampleTable) LOAD

Date

RESIDENT [Master Calendar];

NOCONCATENATE LOAD

[StartDate],

[EndDate],

Date,

WEEK(Date) AS Week

RESIDENT ExampleTable

WHERE DATE(Date ) >= DATE([StartDate]) AND DATE(Date) <= DATE([EndDate])

;

DROP TABLE ExampleTable;

[Anonymous]

Thanks Anil,

Just wondering if I have to use resident load instead of autogenerate?

[Anil_Babu_Samineni]

May be this?

Sample:

Load * From Table;

CalendarMaster:

LOAD Week(Final_Date) As Week;

Load Date(MinDate + IterNo() -1 ) AS Final_Date While Date(MinDate + IterNo() - 1) <= MaxDate;

Load

    Min(startDate) AS MinDate,

    Max(endDate) AS MaxDate

RESIDENT Sample;

[its_anandrjs]

By resident load you can try this way

let startDate  = Num(MakeDate(2015,1,1));
let endDate  = Num(MakeDate(2017,12,30));


tmpCalendar:
Load
Date($(startDate) + IterNo() -1) as Date
AutoGenerate 1
While Date($(startDate) + IterNo() -1) <= $(endDate);


Calendar:
LOAD
*,
Week(Date) as Week
Resident tmpCalendar;


DROP Table tmpCalendar;

[Anonymous]

Anil,

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.

[Anonymous]

is it possible to replace the hard coded dates in the variables with my start and end date columns?

[its_anandrjs]

Yes it is possible but keep the name same and check the variable values also will it return [properly or not.

[Anil_Babu_Samineni]

Okay, Can you make sample and explain exact result set you want to see?