topic Re: Iterate through hours between timestamps in App Development

Iterate through hours between timestamps

covenant_bi — Thu, 19 Dec 2024 20:18:41 GMT

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;

Re: Iterate through hours between timestamps

Clement15 — Fri, 20 Dec 2024 14:36:27 GMT

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;

Re: Iterate through hours between timestamps

Kushal_Chawda — Fri, 20 Dec 2024 16:44:42 GMT

@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;

Re: Iterate through hours between timestamps

covenant_bi — Fri, 20 Dec 2024 17:15:11 GMT

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

Re: Iterate through hours between timestamps

covenant_bi — Fri, 20 Dec 2024 17:25:49 GMT

Thank you @Clement15

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