Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rustyfishbones
Master II
Master 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

2017-07-28_1412.png

instead of this

2017-07-28_1413.png

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hi Alan,

Maybe:

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

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

[Master Clock]:

LOAD

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

LOAD

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]:

LOAD

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

LOAD

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

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

View solution in original post

10 Replies
sunny_talwar

May be use a master time table

The Master Time Table

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

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

data:

LOAD

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

AutoGenerate(365)

;

Join(data)

LOAD

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

AutoGenerate 16*60 + 1

;

data_f:

LOAD

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

Resident data

;

Help users find answers! Don't forget to mark a solution that worked for you!
effinty2112
Master
Master

Hi Alan,

Maybe:

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

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

[Master Clock]:

LOAD

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

LOAD

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]:

LOAD

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

LOAD

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

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

Not applicable

Alan, you can simply generate with below statement:

Let vToday = Num(today());

Let vNoofDays = 30;

Load

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

Autogenerate 1

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

rustyfishbones
Master II
Master II
Author

Thank you everyone,

Let me try them and see which suits best

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Smaller,

data:

LOAD

  Date

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

;

LOAD

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

AutoGenerate(365*24*60)

;

Help users find answers! Don't forget to mark a solution that worked for you!
rustyfishbones
Master II
Master II
Author

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


2017-07-28_1545.png

Regards

Alan

effinty2112
Master
Master

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

rustyfishbones
Master II
Master II
Author

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