correspondence table of dates

[zied_ahmed1]

Hello,

I have two date

date 1 ---> 03/04/2018 (day of today )

date 2 -->2018-04-W1

I would like to create a correspondace table to say that dates :

02/04/2018

03/04/2018

04/04/2018

05/04/2018

06/04/2018

07/04/2018

08/04/2018

are 2018-04-W1 ...

Thanks for help

[OmarBenSalem · Accepted solution]

Let varMinDate = Num(date('01/01/2018')); 

Let varMaxDate = Num(date('31/12/2018')); 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS Date, 

              date(TempDate,'YYYY.MM.')&'W'&Ceil(Day(TempDate)/7) as  Time

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

MyTable:

// 'Week'&Ceil(Day(Date)/7) as  MonthlyWeek

LOAD Time INLINE [

Time

2018.04.W1

2018.03.W4

2018.02.W2

2018.05.W3

2018.06.W1

2018.01.W2

2018.02.W4

2018.04.W4

2018.07.W4

2018.08.W3

2018.09.W2

2018.05.W4

2018.06.W1

];

Result:

[zied_ahmed1]

Thank you Omar !!!