Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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!!