topic Re: Time range in QlikView

Time range

Tue, 30 Jun 2015 04:49:26 GMT

Hello Guys,

I have an issue I need to load data from a specific time (from 04:00:00 to 15:00:00).

In my load script in my call table i used the below:

Load

Time(subfield(CallUniversalTime, ' ' ,'hh:mm:ss')) as Time,

SQL SELECT *

FROM "HN_Ondata".dbo.ODCalls where FirstCampaign = '1743'and Time > '04:00:00' and Time < '15:00:00';

However, it does not work.

Any idea?

Thanks for your help.

Kind Regards,

Hasvine

Re: Time range

vivek_niti — Tue, 30 Jun 2015 04:57:00 GMT

Hi,

Try by converting the time to Number format while equating in the Time field.

Re: Time range

Tue, 30 Jun 2015 05:06:00 GMT

Hi Vivek,

Could you give me an example please?

KR,

Hasvine

Re: Time range

jonathandienst — Tue, 30 Jun 2015 05:12:45 GMT

First you need a semicolon before the SQL command:

Load *,

Time(subfield(CallUniversalTime, ' ' ,'hh:mm:ss')) as Time

;

SQL SELECT *

FROM "HN_Ondata".dbo.ODCalls where FirstCampaign = '1743'and Time > '04:00:00' and Time < '15:00:00';

Secondly - test your SQL query in SQL Management Studio (or other tool like Toad). Verify the data type of [Time]. SQL does not have a time data type, so this will be a datetime field. What is the date portion of the field? Perhaps that is why the query is failing.

Re: Time range

jonathandienst — Tue, 30 Jun 2015 05:15:22 GMT

Oh, and if Time is indeed a date time field, your load expression should be

Load *,

Time(CallUniversalTime, 'hh:mm:ss')) as Time;

or, to remove the date portion:

Load *,

Time(Frac(CallUniversalTime), 'hh:mm:ss')) as Time;

Re: Time range

Tue, 30 Jun 2015 08:32:35 GMT

Thank you for your help.

I finally set the filter in the set it in the expression.

KR,

Hasvine