Calculate number of process running each hour

[Anonymous]

Hi everyone,

I have the below data:

Input Table:

ProcessID	StartTime	EndTime
1	4pm	4pm
2	4pm	5pm
3	4pm	6pm
4	6pm	10pm
5	6pm	11pm

Using this Data, i need to calculate the number of process running each hr.

My Final Output Table:(Note: Comment Column is just for reference,Only CountofProcessRunning Should be calculated using the above input table.)

RunningTime	CountofProcessRunning	Comment
4pm	3	Process 1,2,3 are running
5pm	2	Process 2,3 are running
6pm	3	Process 3,4,5 are running
7pm	2	Process 4,5 are running
8pm	2	Process 4,5 are running
9pm	2	Process 4,5 are running
10pm	2	Process 4,5 are running
11pm	1	Process 5 is running

Could anyone pls help in achieving it.

Thanks in advance

[awhitfield]

Have a look at using interval match, there's a useful example on

https://community.qlik.com/thread/93656

Regards

Andy

[MK_QSL · Accepted solution]

Temp:

Load

  ProcessID,

  Time(Time#(StartTime,'hhTT'),'hh TT') as StartTime,

  Time(Time#(EndTime,'hhTT'),'hh TT') as EndTime

Inline

[

  ProcessID, StartTime, EndTime

  1, 4pm, 4pm

  2, 4pm, 5pm

  3, 4pm, 6pm

  4, 6pm, 10pm

  5, 6pm, 11pm

];

NoConcatenate

Temp2:

Load

  ProcessID,

  StartTime as StartTimeActual,

  Time(StartTime + Time#(IterNo()-1,'hh')) as StartTime,

  EndTime

Resident Temp

While Time(StartTime + Time#(IterNo() - 1,'hh')) <= EndTime;

Drop Table Temp;

Final:

Load

  StartTime as RunningTime,

  COUNT(ProcessID) as CountofProcessRunning

Resident Temp2

Group By StartTime;

Drop Table Temp2;

[mjayachandran]

You can try something like this

T:

LOAD * INLINE [

    ProcessID, StartTime, EndTime

    1, 4pm, 4pm

    2, 4pm, 5pm

    3, 4pm, 6pm

    4, 6pm, 10pm

    5, 6pm, 11pm

];

T2:

Load

StartTime,

StartTime as  RunningTime,

concat(ProcessID,';') as List,

count(ProcessID) as CountofProcessRunning

Resident T Group by StartTime;

Then have a table box with
RunningTime CountofProcessRunning and List

[jonathandienst]

Or with Interval Match:

ProcessRanges:

LOAD

  Time(Time#(StartTime, 'hhtt')) As StartTime,

  Time(Time#(EndTime, 'hhtt')) As EndTime,

  ProcessID

Inline

[

ProcessID StartTime EndTime

1 4pm 4pm

2 4pm 5pm

3 4pm 6pm

4 6pm 10pm

5 6pm 11pm

] (delimiter is '\t') ;

Periods:

LOAD

  Time((-1 + RowNo())/24) As PeriodStart,

  Time((0 + RowNo())/24 - 1/86400) As PeriodEnd

AutoGenerate 24;

Left Join (Periods)

IntervalMatch (PeriodStart)

LOAD StartTime, EndTime

Resident ProcessRanges;

Now use create a table using PeriodStart (and PeriodEnd if you like) as dimension(s) and Count(ProcessID) as expression.

HTH

Jonathan

[Anonymous]

Thanks everyone.Its working perfectly:)