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

[andrespa]

Having this some issue and I've been trying for last 2 hours and I'm not able to find a solution. It's clear that the problem is due to the no evaluation of the variables, but I see that the values get properly stored in the temp table, but then for some reason these 2 lines don't retrieve me the desired values:

LET vMinDate = Num(Peek('MinDate', 0, 'TempCalendar'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'TempCalendar'));

 Any idea what could be happening? It has to be maybe with the date format?

Best Regards,

Andrés

[marcus_sommer]

It's properly related to the comma as decimal delimiter which is valid within a table because it's there just a formatting but the variable takes this value directly and this is then no valid number by calling the variable.

If you don't need the time-part then you could cut it with floor() and if the information is wanted the num() could be adjusted, maybe like: num(Value, '.', '') or a replace() is used.

[andrespa]

Hi Marcus, 

I feel the solution goes in this direction. But even with the floor() function for some reason the number doesn't get evaluated in the variable:

[marcus_sommer]

You are calling the wrong fields within the peek() because you have a qualify-statement before the TempCalendar and therefore the fields are: TempCalendar.MinDate & TempCalendar.MaxDate.

You may adjust the call or probably better removing the qualify. I suggest to avoid it in general because it's causing usually much more trouble and efforts as it's be useful.

[andrespa]

Hi Marcus, true. I've forgot about the qualify. However, after removing it and double check the names, it's still not working:

[marcus_sommer]

Try the variable-assignment without the num() - floor() and min/max in the load are removing each formatting from the values and therefore the num() isn't necessary.