Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

rustyfishbones
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

2017-07-28_1412.png

instead of this

2017-07-28_1413.png

1 Solution

Accepted Solutions
effinty2112
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]:

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

9 Replies

Re: Generate All minutes for each Day

May be use a master time table

The Master Time Table

Partner
Partner

Re: Generate All minutes for each Day

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

;

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

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

Not applicable

Re: Generate All minutes for each Day

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
Honored Contributor II

Re: Generate All minutes for each Day

Thank you everyone,

Let me try them and see which suits best

Partner
Partner

Re: Generate All minutes for each Day

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)

;

rustyfishbones
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


2017-07-28_1545.png

Regards

Alan

Highlighted
effinty2112
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

rustyfishbones
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!!