Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Chart over time period's where startdtime < period and endtime > period

Given is a table with the following values:

NameStarttimeEndtime
A8:0010:00
B7:009:00
...

I want to show a barchart with 48 bars where every bar is a time period of an half hour (7:00, 7:30, 8:00, 8:30)

Each Bar should count the count of entries of the given table where the starttime is before the period and the endtime is after the period. The resultset should be:

PeriodNumber of entries
7:001
7:301
8:002
8:302
9:002
9:301
10:001

My thoughts were to create a virtual table for the The Period's and connect them to the Data, but I have no idea how to connect the both sets, cause there is no unique foreign key for that it's kind of a "between-join"

Do you have any idea's to solve that?

1 Solution

Accepted Solutions
Not applicable
Author

Temp:

Load

  RowNo() as Row,

  mod(RowNo(),60) as Minutes,

  mod(div(RowNo(),60),(24)) as Hours

AutoGenerate (24*60);

Temp2:

Load

  Hours,

  Minutes,

  Hours&':'&Minutes as Time,

    Hours&':'&if(Minutes<30,00,30)&' to '&Hours&':'&if(Minutes<30,29,59) as Bucket

//   Hours&':'&Class(Minutes,30) as Bucket2

Resident Temp;

DROP Table Temp;

Try to Map this

View solution in original post

5 Replies
Not applicable
Author

Hi ,

Yes you can create a virtual period table and connect it to the data using INTERVAL MATCH. Check the forum for examples.

Not applicable
Author

When I have an entry C with Starttime = 8:10 and Endime=8:20 it will not be connected with 8:00 but in my case it has to be counted, because it is between 8:00 and 8:30

Not applicable
Author

Instead of creating a virtual table with period of 30 minustes, you can create a virtual table for every minute using autogenerate and the do the interval match.

Not applicable
Author

And how do I ensure that the chart later shows a bar for each 30-minutes step?

Not applicable
Author

Temp:

Load

  RowNo() as Row,

  mod(RowNo(),60) as Minutes,

  mod(div(RowNo(),60),(24)) as Hours

AutoGenerate (24*60);

Temp2:

Load

  Hours,

  Minutes,

  Hours&':'&Minutes as Time,

    Hours&':'&if(Minutes<30,00,30)&' to '&Hours&':'&if(Minutes<30,29,59) as Bucket

//   Hours&':'&Class(Minutes,30) as Bucket2

Resident Temp;

DROP Table Temp;

Try to Map this

View solution in original post