Counting concurrent tasks with set analysis

[veronica1]

Hi everyone!

Once again, I'm writing here because it seems I cannot find the Qlik Sense forum when I ask a question (any suggestions are appreciated).

Basically I have several tasks with StartTime and EndTime:

ReloadID	App	StartTime	EndTime
1	Demo	19/04/2023 08:08	19/04/2023 08:17
2	Sales	19/04/2023 08:01	19/04/2023 09:12
3	Finance	19/04/2023 09:15	19/04/2023 09:37
4	Health	19/04/2023 09:31	19/04/2023 09:41

I'd like to count the reloads for each hour of the day. For example:

07:00-08:00	0
08:00-09:00	2
09:00-10:00	3

How can I achieve that? I'd also like to not duplicate rows with outer joins.

Thanks so much in advance

[Chanty4u]

Hi try this 

LOAD

    ReloadID,

    App,

    StartTime,

    EndTime,

    HOUR(StartTime) AS StartHour

FROM [path\filename.xlsx]

(ooxml, embedded labels, table is [Sheet1$]);

LOAD

    0 AS HourStart,

    7 AS HourEnd,

    '00:00-07:00' AS HourRange

AUTOGENERATE 1;

LOAD

    n+7 AS HourStart,

    n+8 AS HourEnd,

    num#(n+7, '00') & ':00-' & num#(n+8, '00') & ':00' AS HourRange

RESIDENT [Table] WHILE n < 16;

RELOAD:

LOAD

    HourRange,

    COUNT(DISTINCT ReloadID) AS ReloadCount

RESIDENT [Table]

GROUP BY HourRange;

JOIN (RELOAD)

LOAD

    *

RESIDENT [Data]

WHERE StartHour >= HourStart AND StartHour < HourEnd

GROUP BY HourRange;