Time Intervals in a day

[#1]

Hi

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!

[Ralf-Narfeldt]

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;

[jagan]

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.

[#4]

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!

[jagan · Accepted solution]

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.

[Ralf-Narfeldt]

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

[#7]

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?

[Ralf-Narfeldt]

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

Preceding Load

[jagan]

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.