topic Re: Time Intervals in a day in QlikView

Time Intervals in a day

Tue, 14 Apr 2015 06:01:36 GMT

Rather new to QlikView here, and I've tried to search for solutions on the community but am unable to find any.

I have a data file with transactional data in the following format:

I would like to have a table in QV that lists the sales time in intervals:

e.g.

How should I go about doing this?

I've currently copied a code from another community page:

Dual(

Interval(SubField(Class([Sale Time], 1/24),'<= x <',1),'HH:mm')&' - '&

Interval(SubField(Class([Sale Time], 1/24),'<= x <',2),'HH:mm'),

Class([Sale Time], 1/24)

) as [Sale Time Period]

but when I viewed the data in QlikView, I'm getting the following:

So obviously I'm missing out something here as the Sale Time is not linked to the correct time period.

Would appreciate it if anyone is able to point me in the right direction!

Re: Time Intervals in a day

Ralf-Narfeldt — Tue, 14 Apr 2015 09:21:19 GMT

If you just need to display it, this simple expression should work:

Hour([Sale Time]) & ':00-' & Hour([Sale Time]) & ':59' As [Sale Time Period]

If you want to add a bucket for all possible time periods, add this table as well:

SaleTimePeriods:

LOAD

Num(RecNo()-1,'##') & ':00-' & Num(RecNo()-1,'##') & ':59' As [Sale Time Period]

Autogenerate 24;

Re: Time Intervals in a day

jagan — Tue, 14 Apr 2015 09:50:06 GMT

Hi,

Try like this in script

LOAD

Hour([Sale Time]) & ':00-' & Hour([Sale Time]) + 1 & ':00' As [Sale Time Period]

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Time Intervals in a day

Wed, 15 Apr 2015 01:29:41 GMT

Hi Ralf

I've tried adding your script as mentioned (kindly see attached).

The top chart and list box were created using the script I mentioned in my original post, while the bottom chart and list box was obtained from your script.

As you can see, the bottom chart and list box do not list the data correctly, nor do they update correctly.

Will it be possible to take a look at the script and see what I'd done wrong? Your help is much appreciated!

Re: Time Intervals in a day

jagan — Wed, 15 Apr 2015 02:24:54 GMT

Hi,

You are joining the Transaction Data and Transaction Date tables based on the Date but it should be joined based on time also. You can solve this by using the below script

[Transaction Data]:

LOAD *,

Year([Sale Date]) as [Sale Year],

MONTH([Sale Date]) as [Sale Month],

DAY([Sale Date]) as [Sale Day],

Hour([Sale Time]) & ':00-' & Hour([Sale Time]) & ':59' As [Sale Time Period];

LOAD TXN_ID,

Date(Date#(left(TXN_ID,8),'YYYYMMDD'),'DD/MM/YYYY') as [Sale Date],

TXN_CODE,

SALES_DATE,

Date(Date#(right(SALES_DATE,5),'hh:mm'),'hh:mm') as [Sale Time],

PATRON_ID,

CHANNEL_TYPE,

REFERENCED_TXN_ID,

PRODUCT_ID,

TICKETS_SOLD,

PAYMENT_METHOD,

PAID_AMT,

POS_ALIAS

FROM

(biff, embedded labels, table is [Export Worksheet$]);

Hope this helps you.

Regards,

Jagan.

Re: Time Intervals in a day

Ralf-Narfeldt — Wed, 15 Apr 2015 05:18:11 GMT

Jagan's solution looks about right, can't verify without source data files.

Re: Time Intervals in a day

Wed, 15 Apr 2015 06:06:58 GMT

Hi Jagan

Tried your code and it works perfect. Thanks for the help!

As I have no programming background, I'm not too sure how the scripting works, shouldn't it load the top "Load *" statement first before the bottom "Load" statement? Is there any documentation anywhere that can point me in the right direction?

Re: Time Intervals in a day

Ralf-Narfeldt — Wed, 15 Apr 2015 06:10:38 GMT

That's a preceding LOAD, a great feature. HIC has written most of what you need to know in this blog post:

Preceding Load

Re: Time Intervals in a day

jagan — Wed, 15 Apr 2015 06:17:22 GMT

Hi,

That is called Preceding Load, you can find this type of tips and scripting in Qlikview Reference Manual PDF or you can purchase Qlikview 11 for Developers book.

Regards,

Jagan.