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

[swuehl]

Just for clarification, I believe Jason is talking about the first From in your second load:

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;

I would also use AS statements for the fields only once per field (I am not sure if it matters though), probably in the LOAD:

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;

SQL select raw_value,

date_in

from ocom_ob_a_cap;

[Jason_Michaelides]

Thanks Stefan - I was indeed talking about that. And thanks too for spotting the double use of the aliases (although you've missed one - date_in doesn't exist to use in the load as it's been aliased in the script as common_date!)

Wrote the response on the train and didn't read it over - oops!

[swuehl]

No problem, I know how it is to write on the fly

I justed wanted to avoid some confusion.

I corrected the date_in alias in the SQL Part.

Have a nice evening / night,

Stefan

[#5]

thanks a bunch guys.  I will give it a try.

I really appreciate all of  the input

[Jason_Michaelides]

Stick at it and you'll soon be helping others in here too! Let us know how you get on with this.

[#7]

You guys are the bees knees!  It has successfully reloaded. 

thanks for everything

Marshall

Orbitz

[Jason_Michaelides]

Cool - glad it worked. Don't forget to credit correct/helpful answers!

[Emmanuelle__Bustos]

Hello fixxer400  as a good practice you should mark the correct Answer so this helps to other users to verify quickly the best articles. 

Regards.

[#10 · Accepted solution]

You are absolutly correct!

BTW here is a piece of my load script that I am going with.  Thanks to everyone that looked in on this.  I am new to this product and environment.  I am blown away by all of the cool folks that pitched in:

Load raw_value as ocom_ob_dp_raw,

date_in as ocom_ob_dp_date,

hour(date_in) as hour_ocom_ob_dp_date,

day(date_in) as day_ocom_ob_dp_date,

week(date_in) as week_ocom_ob_dp_date,

month(date_in) as month_ocom_ob_dp_date,

year(date_in) as year_ocom_ob_dp_date,

//date(date_in, 'YYYY-MMM-DD-HH') as yearmonthdayhour_ob_date,

date(date_in, 'YYYY-MMM-DD') & '-' & hour(date_in) as yearmonthdayhour_ocom_ob_dp_date,

date_in as common_date;

select raw_value ,date_in  from ocom_ob_dp_a_cp;

Thanks again

Marshall

fixxer400

[Jason_Michaelides]

A little tip about using Week(Date), Month(Date), Year(Date) and dates in general...

The problem with your dates being in a transactional (fact) table is that a particular date will only exist in the overall data set if it exists in the fact table i.e. if there was activity on that day.  This can make charts with a date dimension unpredictable (there may be gaps you don't notice). You might want to consider creating a separate calendar table that has a record for every date within the range you want to report on.  In this calendar table put all the derivative values you want (Week, WeekDay, Month, Year, Quarter, FinancialYear etc) and allow it to link to the fact table on Date.  Your data set now has a value for every date you require and your fact table is a little cleaner.

There are several good example documents up here that provide the required script.  Have a look around.

Jason