Iterate through hours between timestamps

[covenant_bi]

Hello, we are wanting to iterate through hours between timestamps.  We want ultimately want to bucket our IDs by hours of the day and a department.  We are familiar with this technique using dates

IterNo - script function | QlikView Help

But we want to take it step further and iterate through the hours of the day from the start time to the end time of each department the ID appears in.  In the attached Excel, I have the source data as we have it and then how we want it to appear.

We are trying to something like the IterNo technique above but it's not quite working out. 

Data:
LOAD *
    ,IterNo() as Count
    ,Timestamp(MinDate+(1/24))+IterNo()-1 as Hourly
    Resident TempHourly
    While MinDate+(1/24) + IterNo() -1 <=MaxDate
    ;
    
    Drop Table TempHourly;

[Clement15]

Hello,

Here it is, it should work. Sorry for the floor() and the weird additions but the rounding of hours works weirdly, but like this, all the hours are taken.

TempHourly:

LOAD

    ID,

    DEPARTMENT,

    "timestamp(MinDate)" as [timestamp(MinDate)],

    "timestamp(MaxDate)" as [timestamp(MaxDate)]

FROM [lib://DataFiles/sample data (1).xlsx]

(ooxml, embedded labels, table is Sheet1)

where ID <> null();




MinMaxDates:

LOAD

    num(Min([timestamp(MinDate)])) as MinDateTime,

    num(Max([timestamp(MaxDate)])) as MaxDateTime

RESIDENT TempHourly;




// Stocker les dates minimales et maximales dans des variables

LET vMinDateTime = Peek('MinDateTime', 0, 'MinMaxDates');

LET vMaxDateTime = Peek('MaxDateTime', 0, 'MinMaxDates');




// Créer une table temporaire pour les heures

TempHours:

LOAD

    floor(num('$(vMinDateTime)' + (IterNo()-1)/24)+ 0.000001,1.0/24.0)-0.00000000001 AS NumHour

AUTOGENERATE 1

WHILE '$(vMinDateTime)' + (IterNo()-2)/24 <= '$(vMaxDateTime)';




NoConcatenate




// Charger la table finale des heures

Heures:

LOAD

    NumHour,

    Timestamp(NumHour, 'DD/MM/YYYY hh:mm') as Hour

RESIDENT TempHours;

Drop table TempHours;




inner Join(TempHourly)

TEST2:

IntervalMatch(Hour)

Load

  

    [timestamp(MinDate)],

    [timestamp(MaxDate)]

Resident TempHourly;

[Kushal_Chawda · Accepted solution]

@covenant_bi  If your Dates are in proper timestamp format, you can simply try below. If not, before taking resident convert date into proper timestamp for using Timestamp#()

Load *,
     timestamp(MinDate + (IterNo()-1)/24) as Time_Hourly
Resident TempHourly
While MinDate + (IterNo()-1)/24<=MaxDate;

drop table TempHourly;

[covenant_bi]

Thanks @Kushal_Chawda! I guess we were close, just had the /24 in the wrong place!

[covenant_bi]

Thank you @Clement15 

This worked as well. I like the Intervalmatch and autogenerate of the hourly data in it's own table