Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Time range

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

5 Replies
vivek_niti
Contributor

Re: Time range

Hi,

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

Not applicable

Re: Time range

Hi Vivek,

Could you give me an example please?

KR,

Hasvine

MVP
MVP

Re: Time range

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Time range

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;


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Time range

Thank you for your help.

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

KR,

Hasvine

Community Browser