topic Re: Having trouble with Master Time in QlikView

Having trouble with Master Time

Fri, 21 Nov 2014 20:31:57 GMT

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;

Re: Having trouble with Master Time

maxgro — Fri, 21 Nov 2014 21:20:53 GMT

case and 0?

Let varMinTime = (Num(peek('minDate', 0)));

Let varMaxTime = (Num(Peek('maxDate', 0)));

Re: Having trouble with Master Time

MarcoWedel — Fri, 21 Nov 2014 22:55:50 GMT

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