Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
karthigayan
Creator II
Creator II

Calculate number of process running each hour

Hi everyone,

I have the below data:

 

Input Table:

ProcessIDStartTimeEndTime
14pm4pm
24pm5pm
34pm6pm
46pm10pm
56pm11pm

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.)

   

   

RunningTimeCountofProcessRunningComment
4pm3Process 1,2,3 are running
5pm2Process 2,3 are running
6pm3Process 3,4,5 are running
7pm2Process 4,5 are running
8pm2Process 4,5 are running
9pm2Process 4,5 are running
10pm2Process 4,5 are running
11pm1Process 5 is running

Could anyone pls help in achieving it.

Thanks in advance

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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;

View solution in original post

5 Replies
awhitfield
Partner - Champion
Partner - Champion

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

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

Regards

Andy

MK_QSL
MVP
MVP

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
Creator II
Creator II

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
karthigayan
Creator II
Creator II
Author

Thanks everyone.Its working perfectly:)