create an hourly chart from existing minute by minute data

[#1]

I have done a select that contains a minute by minute array of data.  It displays nicely on a minute to minjute basis on the x-axis.  Is there a way roll uo and display the data on an hourly basis?

Is there a weasy to modify the slect statement or perhhaps a qli]kview call that can convert the data to hourly.

Thanks for any hwlp.

fixxer400

[Jason_Michaelides]

Hour(TimeField)

Consider putting Hour(TimeField) AS Hour in your script.

Hope this helps,

Jason

[#3]

Cool, thanks I will try it right!

thanks

[#4]

I unfortunatly get an error saying that the function "hour(timestanp without timezone) does not exist.

here is my select statement:

select raw_value as ocom_ob_raw,date_in as ocom_ob_date,hour(date_in) as hour_ob_date,date_in as common_date from ocom_ob_a_cap

what am I doing wrong?

[Jason_Michaelides]

The Hour() function is a QlikView one, not SQL. Difficult to say without seeing your application but try:

Load raw_value as ocom_ob_raw,date_in as ocom_ob_date,hour(date_in) as hour_ob_date,date_in as common_date from ocom_ob_a_cap;

SQL select raw_value as ocom_ob_raw,date_in as ocom_ob_date,date_in as common_date from ocom_ob_a_cap;

[swuehl]

Hi,

you need to use a LOAD statement to be able to use QV functions like hour while reading in date:

LOAD *,

hour(date_in) as hour_ob_date;

SQL Select ...;

Regards,

Stefan

[#7]

Thanks

A lot of us at orbitz are new to the qlikview app so we are totally winging it.

I will give it a try,

Thanks again

[#8]

Cool thanks

I am a newbie to this

[#9]

I know I must be doing something stupid but I got this error:

Cannot open file 'C:\kieth-new\ocom_ob_a_cap'

Load raw_value as ocom_ob_raw,date_in as ocom_ob_date,hour(date_in) as hour_ob_date,date_in as common_date from ocom_ob_a_cap

I am drawing data from a postgress sql databasde which I have connected to via ODBC previous toal of the selects, below is the opening part of my load

script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO PostgreSQL30 (XUserId is FODGQWJOPbdKWfD, XPassword is CLGRSWJOPbdKWGB);

LOAD latency_market,

     latency_wl,

     app,

     ssid,

     difference,

     date_in

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Load raw_value as ocom_ob_raw,date_in as ocom_ob_date,hour(date_in) as hour_ob_date,date_in as common_date from ocom_ob_a_cap;

SQL select raw_value as ocom_ob_raw,date_in as ocom_ob_date,date_in as common_date from ocom_ob_a_cap;

Where am I screwing up?

thanks

fixxer400

[Jason_Michaelides]

Sorry mate - mistake in my copy and paste of your code! Get rid of the first FROM statement.