topic Re: Generate 1 row per hour when id stay enable in QlikView

Generate 1 row per hour when id stay enable

Anonymous — Thu, 21 Jan 2016 17:52:22 GMT

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)

Re: Generate 1 row per hour when id stay enable

maxgro — Thu, 21 Jan 2016 19:05:09 GMT

try

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;

Re: Generate 1 row per hour when id stay enable

Anonymous — Thu, 21 Jan 2016 19:26:49 GMT

Thanks, but using the floor function, the first row would be 06:00 instead 07:00 ?

Re: Generate 1 row per hour when id stay enable

Anonymous — Thu, 21 Jan 2016 19:27:06 GMT

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

Re: Generate 1 row per hour when id stay enable

maxgro — Thu, 21 Jan 2016 19:38:53 GMT

start + iterno()/24 in the while loop is like start + 1 hour, start + 2 hour, .....

floor(................., 1/24) will floor to 00 min

Re: Generate 1 row per hour when id stay enable

MarcoWedel — Sat, 23 Jan 2016 01:17:58 GMT

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