topic Re: Generate All minutes for each Day in QlikView

Generate All minutes for each Day

rustyfishbones — Fri, 28 Jul 2017 13:15:44 GMT

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

instead of this

Re: Generate All minutes for each Day

sunny_talwar — Fri, 28 Jul 2017 13:27:54 GMT

May be use a master time table

The Master Time Table

Re: Generate All minutes for each Day

Aurelien_Martinez — Fri, 28 Jul 2017 13:43:42 GMT

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

;

Re: Generate All minutes for each Day

effinty2112 — Fri, 28 Jul 2017 13:47:25 GMT

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

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)

Re: Generate All minutes for each Day

Fri, 28 Jul 2017 13:50:31 GMT

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;

Re: Generate All minutes for each Day

rustyfishbones — Fri, 28 Jul 2017 13:57:09 GMT

Thank you everyone,

Let me try them and see which suits best

Re: Generate All minutes for each Day

Aurelien_Martinez — Fri, 28 Jul 2017 14:03:41 GMT

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)

;

Re: Generate All minutes for each Day

rustyfishbones — Fri, 28 Jul 2017 14:46:36 GMT

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

Re: Generate All minutes for each Day

effinty2112 — Fri, 28 Jul 2017 14:51:55 GMT

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

Re: Generate All minutes for each Day

rustyfishbones — Fri, 28 Jul 2017 15:27:42 GMT

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

Re: Generate All minutes for each Day

santosh4195 — Sun, 20 Mar 2022 00:04:09 GMT

Hello,

I am also facing the same issue. Please let me know if you are able to solve that problem.

Thank you