# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Honored Contributor II

## Generate All minutes for each Day

Hi All,

I have the following in my script to generate OneMinuteBuckets from a StartTime

TIME(FLOOR(TIME#(StartTime,'hh:mm:ss'),1/24/60),'hh:mm')AS OneMinBucket,

The problem I have is, I always show the data in the chart for 1 day, but I want to see all minutes from 6am to 11pm.

But if there were no transactions in a particular minute (which can happen regularly), I still want to generate a dimension for that minute

So if a worker does the first transaction at 09:01 and the second at 09:05

I still want to display 09:01,09:02,09:03,09:04,09:05

So I want my chart would look similar to below

Tags (5)
1 Solution

Accepted Solutions
Highlighted
Honored Contributor

## Re: Generate All minutes for each Day

Hi Alan,

Maybe:

Let vStart = Time#('06:00','hh:mm');

Let vEnd = Time#('23:00','hh:mm');

[Master Clock]:

Time(Temp_Time/1440) as [Clock Time];

num('\$(vStart)')*24*60 -1+  IterNo() as Temp_Time

AutoGenerate 1 While num('\$(vStart)')*24*60 + IterNo() -1<=  num('\$(vEnd)')*24*60;

Cheers

Andrew

PS

you might prefer this

Let vStart = Time#('06:00','hh:mm');

Let vEnd = Time#('23:00','hh:mm');

Let vStartMinute = num('\$(vStart)')*24*60;

Let vEndMinute = num('\$(vEnd)')*24*60;

[Master Clock]:

Time(Temp_Time/1440) as [Clock Time];

\$(vStartMinute) -1+  IterNo() as Temp_Time

AutoGenerate 1 While \$(vStartMinute) + IterNo() -1<=  \$(vEndMinute)

9 Replies
Highlighted
MVP

## Re: Generate All minutes for each Day

May be use a master time table

The Master Time Table

Highlighted
Partner

## Re: Generate All minutes for each Day

Hi,

This script generate all date time during one year and 6am to 11pm :

data:

Date(Today() + RecNo()) as Date

AutoGenerate(365)

;

Join(data)

Time(Time#(6, 'hh') + Time#(RecNo()-1, 'mm'), 'hh:mm') as time

AutoGenerate 16*60 + 1

;

data_f:

Date#(Date & ' ' & time, 'DD/MM/YYYY hh:mm') as dateTime

Resident data

;

Highlighted
Honored Contributor

## Re: Generate All minutes for each Day

Hi Alan,

Maybe:

Let vStart = Time#('06:00','hh:mm');

Let vEnd = Time#('23:00','hh:mm');

[Master Clock]:

Time(Temp_Time/1440) as [Clock Time];

num('\$(vStart)')*24*60 -1+  IterNo() as Temp_Time

AutoGenerate 1 While num('\$(vStart)')*24*60 + IterNo() -1<=  num('\$(vEnd)')*24*60;

Cheers

Andrew

PS

you might prefer this

Let vStart = Time#('06:00','hh:mm');

Let vEnd = Time#('23:00','hh:mm');

Let vStartMinute = num('\$(vStart)')*24*60;

Let vEndMinute = num('\$(vEnd)')*24*60;

[Master Clock]:

Time(Temp_Time/1440) as [Clock Time];

\$(vStartMinute) -1+  IterNo() as Temp_Time

AutoGenerate 1 While \$(vStartMinute) + IterNo() -1<=  \$(vEndMinute)

Highlighted
Not applicable

## Re: Generate All minutes for each Day

Alan, you can simply generate with below statement:

Let vToday = Num(today());

Let vNoofDays = 30;

\$(vToday) - ((IterNo()-1)/(24*60)) as DateTimeField

Autogenerate 1

While IterNo() < \$(vNoofDays) * 1440;

Highlighted
Honored Contributor II

## Re: Generate All minutes for each Day

Thank you everyone,

Let me try them and see which suits best

Partner

## Re: Generate All minutes for each Day

Smaller,

data:

Date

Where Num#(Timestamp(Date, 'hh')) >=6 and Num#(Timestamp(Date, 'hh')) <= 22

;

Date(Today() + Date#(RecNo(), 'mm'), 'DD/MM/YYYY hh:mm') as Date

AutoGenerate(365*24*60)

;

Highlighted
Honored Contributor II

## Re: Generate All minutes for each Day

Hi All,

I think I have the minutes sorted, so see the attached where my chart will not show the white space between non pick transactions

Green Means a pick happened for more than  2 cases in that minute

Red Means a pick happened for less than  2 cases in that minute

White means no pick happened in that minute

I am hoping for something similar to this, but by minute rather than Days

Regards

Alan

Highlighted
Honored Contributor

## Re: Generate All minutes for each Day

Hi Alan,

You could try the old trick of adding Sum({1}0) to your expression so it will return a 0 rather than null. It's not perfect - it can become a headache if you're making anything other than simple selections.

cheers

Andrew

Highlighted
Honored Contributor II

## Re: Generate All minutes for each Day

Tried that and it didn't work - I have been trying to get this chart working for months now!!