Problem formatting time...i think

[qw_johan]

Hi

I am looking for help with the code below. I don't understand where I am going wrong with this.

My guess is it is a formatting problem with time ie. my_time, something I am doing wrong.

What I want to achieve is to be able to display if a specific time is in the morning, day, evening or night.

I am reading many timestamps from an excel spredsheet. See attached qvw...

I added the excel file, maybe that will help.

Thankful for any help.

TEMP_TEST_TIMESTAMP:

LOAD

          @1 as my_Timestamp_original,

          Timestamp(@1, 'YYYY-MM-DD hh:mm:ss') as my_Timestamp,

  Date(Daystart(timestamp(@1)), 'YYYY-MM-DD') as my_date,

  Time(Timestamp(@1, 'YYYY-MM-DD hh:mm:ss'), 'hh:mm:ss') as my_time,

          len(@1) as my_Timestamp_original_length

FROM

LB179_20120924_194125.xls(biff, no labels, table is Sheet1$);

TEST_TIMESTAMP:

load *, len(my_Timestamp) as my_Timestamp_length Resident TEMP_TEST_TIMESTAMP;

drop table TEMP_TEST_TIMESTAMP;

TIMEPERIOD:

LOAD * INLINE [

TimeFrom, TimeTo, TimePeriod

00:00:00, 06:00:00, Night

06:00:01, 10:00:00, Morning

10:00:01, 18:00:00, Day

18:00:01, 23:00:00, Evening

23:00:01, 23:59:59, Night

];

Left join (TEST_TIMESTAMP)

IntervalMatch(my_time)

LOAD time(TimeFrom, 'hh:mm:ss') as TimeFrom, time(TimeTo, 'hh:mm:ss') as TimeTo RESIDENT  TIMEPERIOD;

Left Join (TEST_TIMESTAMP)

LOAD * RESIDENT TIMEPERIOD;

drop table TIMEPERIOD;

[chematos]

Try with

Inner join (TEST_TIMESTAMP)

instead Left Join

[alexpanjhc]

i think you are right.  when i used ur code and replace the excel file with a inline table, it worked fine.

Maybe upload the excel file, lets see what happens there?

  my inline table btw

LOAD

* INLINE [
my_Timestamp_original, my_Timestamp, my_date, my_time
2012-03-21 00:53:07, 2012-03-21 00:53:07, 2012-03-21, 00:53:07
2012-03-21 06:01:01, 2012-03-21 06:01:01, 2012-03-21, 06:01:01
2012-03-21 10:00:01, 2012-03-21 10:00:01, 2012-03-21, 10:00:01
2012-03-21 18:00:01, 2012-03-21 18:00:01, 2012-03-21, 18:00:01
2012-03-21 23:00:01, 2012-03-21 23:00:01, 2012-03-21, 23:00:01
]

;

[chematos]

Upload the file if you want, you need to make sure that the fields you need to compare have the same type of data, so make equals types of you Excel and QV and that must work

[qw_johan]

I update my post with the excel file.

Thanks for your help

[alexpanjhc · Accepted solution]

i add the timestamp on the excel so it has a column name, but i think that should not matter, and I changed the code of  my_time.

trim(Time(timestamp, 'hh:mm:ss')) as my_time

,

that worked.

qvw attached.

[qw_johan]

Thanks for your quick help.

It was just what I was looking for.

Thanks