Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have a problem in my table... I have datetime of creation and datetime of disable, for example:
ID 1
Datetime of Creation: 2016-01-20 06:21:12
Datetime of Disable: 2016-01-20 22:45:10
In this case will be generate 17 rows, one per hour for every hour the ID 1 stay enable, it's possible to utilising the function IterNo() in this case or another solution?
(I open another discussion because my reply get in moderated post)
try
I:
load
timestamp(Timestamp#(start, 'YYYY-MM-DD hh:mm:ss')) as start,
timestamp(Timestamp#(end, 'YYYY-MM-DD hh:mm:ss')) as end
inline [
start, end
2016-01-20 06:21:12, 2016-01-20 22:45:10
];
load start, end, timestamp(floor(start + iterno()/24, 1/24))
Resident I
while start + (IterNo()-1)/24 <= end;
Thanks, but using the floor function, the first row would be 06:00 instead 07:00 ?
You have a start time and an end time, so the IntervalMatch() function could well help you. It is explained in this blog post, IntervalMatch
start + iterno()/24 in the while loop is like start + 1 hour, start + 2 hour, .....
floor(................., 1/24) will floor to 00 min
Hi,
one example could be:
table1:
LOAD *,
AutoNumberHash128([Datetime of Creation],Ceil([Datetime of Disable],'01:00')) as EnableIntervID;
LOAD *,
Timestamp([Datetime of Creation]+fabs(NORMINV(Rand(),0,1))) as [Datetime of Disable];
LOAD RecNo() as ID,
Timestamp(Today()-Rand()*100) as [Datetime of Creation]
AutoGenerate 1000;
tabCalendar:
LOAD *,
Day(DateTime) as Day,
DayName(DateTime) as Date,
WeekDay(DateTime) as WeekDay,
Week(DateTime) as Week,
WeekName(DateTime) as WeekName,
Month(DateTime) as Month,
MonthName(DateTime) as MonthName,
Dual('Q'&Ceil(Month(DateTime)/3),Ceil(Month(DateTime)/3)) as Quarter,
QuarterName(DateTime) as QuarterName,
Year(DateTime) as Year,
WeekYear(DateTime) as WeekYear;
LOAD Timestamp(Floor(MinDateTime,'01:00')+(IterNo()-1)*'01:00') as DateTime
While Floor(MinDateTime,'01:00')+(IterNo()-1)*'01:00' <= Ceil(MaxDateTime,'01:00');
LOAD Min([Datetime of Creation]) as MinDateTime,
Max([Datetime of Disable]) as MaxDateTime
Resident table1;
tabLink:
IntervalMatch(DateTime)
LOAD Distinct
[Datetime of Creation],
Ceil([Datetime of Disable],'01:00') as [Datetime of Disable]
Resident table1;
Left Join (tabLink)
LOAD Distinct
[Datetime of Creation],
[Datetime of Disable],
AutoNumberHash128([Datetime of Creation],[Datetime of Disable]) as EnableIntervID
Resident tabLink;
DROP Fields [Datetime of Creation],[Datetime of Disable] From tabLink;
hope this helps
regards
Marco