Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey,
So, I am building an app where the users would like to be able to drill down to hour. To do this, I decided to make a Master Calendar and a Master Time sheet. The Master Calendar works fine, but they Master Time is creating issues. Need some help on what is happening and why it won't work. Running it, I see that varMinTime and varMaxTime are not getting populated. I tried FieldName(Timestamp) too.
So, here is where I bring in the timestamp:
Time(Floor(Frac(LINE_CREATION_DATE),1/24/60),'hh:mm') as Timestamp,
And here is the master time:
Temp:
Load
min(Timestamp) as minDate,
max(Timestamp) as maxDate
Resident Bookings;
Let varMinTime = Num(peek('MinDate', 1));
Let varMaxTime = Num(Peek('MaxDate', 1));
DROP Table Temp;
TempTime:
LOAD
DayStart(TimeStamp($(varMinTime) + (RecNo()/60/60/24) + (IterNo() -1))) AS Num,
TimeStamp($(varMinTime) + (RecNo()/60/60/24) + (IterNo() -1), 1/24/60) AS TimeDate
AUTOGENERATE 86399 WHILE $(varMinTime) + IterNo() -1 <= $(varMaxTime);
MasterTime:
Load
TimeDate AS Timestamp,
// Week(TempDate) As Week,
// Year(TempDate) As Year,
// Month(TempDate) As Month,
// Day(TempDate) As Day,
Hour(TimeDate) As Hour,
Minute(TimeDate) as Minute
// Second(TempDate) as Second,
// YeartoDate(TempDate)*-1 as CurYTDFlag,
// YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
// inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
// date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
// //ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
// Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
// WeekDay(TempDate) as WeekDay
Resident TempTime;
//Order By TempDate ASC;
Drop Table TempTime;
case and 0?
Let varMinTime = (Num(peek('minDate', 0)));
Let varMaxTime = (Num(Peek('maxDate', 0)));
case and 0?
Let varMinTime = (Num(peek('minDate', 0)));
Let varMaxTime = (Num(Peek('maxDate', 0)));
Hi,
there is no need for the variable/temp table approach, you can use preceding loads instead.
Compare to this somewhat similar script:
Re: Re: Start date and end date in two different rows
BeginCalendar:
Load Begin,
Week(Begin) As Begin_Week,
Year(Begin) As Begin_Year,
Month(Begin) As Begin_Month,
Day(Begin) As Begin_Day,
YeartoDate(Begin)*-1 as Begin_CurYTDFlag,
YeartoDate(Begin,-1)*-1 as Begin_LastYTDFlag,
InYear(Begin, Monthstart(maxDate),-1) as Begin_RC12,
Date(MonthStart(Begin), 'MMM-YYYY') as Begin_MonthYear,
Dual('Q'&Ceil(Month(Begin)/3),Ceil(Month(Begin)/3)) as Begin_Quarter,
Dual(Week(WeekStart(Begin))&'-'&WeekYear(Begin),WeekStart(Begin)) as Begin_WeekYear,
WeekDay(Begin) as Begin_WeekDay;
LOAD Date(minDate+IterNo()-1) as Begin,
maxDate
While minDate+IterNo()-1<=maxDate;
LOAD Min(Begin) as minDate,
Max(Begin) as maxDate
Resident tabCompRisk;
hope this helps
regards
Marco