Calendar

[shamitshah]

Hi,

I am having an issue with the script below to create a temp calendar. Strangely, it used to work but now it is coming with an error message.Not sure how to resolve it.

Temp: 

Load 

               Min(SaleDate) as MinDate, 

               Max(SaleDate) as MaxDate 

Resident SALES; 

Let vMinDate = Num(Peek('MinDate', 0, 'Temp')); 

Let vMaxDate = Num(Peek('MaxDate', 0, 'Temp')); 

TempCalendar: 

LOAD 

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

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

AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

DROP Table Temp;

Thanks

Shamit

[Frank_Hartmann]

Try like this:

TempCalendar:

LOAD

$(vMinDate) + RowNo() - 1 AS Num,

Date($(vMinDate) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

hope that helps

[sorrakis01 · Accepted solution]

HI,

It works:

SALES:

LOAD * INLINE [

SaleDate

10/01/2016

30/03/2016

];

Temp:

Load

               Min(SaleDate) as MinDate,

               Max(SaleDate) as MaxDate

Resident SALES;

Let vMinDate = Num(Peek('MinDate', 0, 'Temp'));

Let vMaxDate = Num(Peek('MaxDate', 0, 'Temp'));

TempCalendar:

LOAD

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

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

AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

DROP Table Temp;

Regards

[shamitshah]

Hi Jordi,

Thanks , I tried it again and it appears to work.

I think the issue I had was that SALES table had no values due to an incorrect filter.

Shamit