topic Re: Problem formatting time...i think in QlikView

Problem formatting time...i think

qw_johan — Mon, 24 Sep 2012 18:49:11 GMT

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;

Re: Problem formatting time...i think

chematos — Mon, 24 Sep 2012 19:05:56 GMT

Try with

Inner join (TEST_TIMESTAMP)

instead Left Join

Re: Problem formatting time...i think

alexpanjhc — Mon, 24 Sep 2012 19:39:36 GMT

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
]

;

Re: Problem formatting time...i think

chematos — Mon, 24 Sep 2012 19:50:30 GMT

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

Re: Problem formatting time...i think

qw_johan — Mon, 24 Sep 2012 20:26:56 GMT

I update my post with the excel file.

Thanks for your help

Re: Problem formatting time...i think

alexpanjhc — Mon, 24 Sep 2012 20:38:13 GMT

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.

Re: Problem formatting time...i think

qw_johan — Tue, 25 Sep 2012 18:55:50 GMT

Thanks for your quick help.

It was just what I was looking for.

Thanks