Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qw_johan
Contributor

Problem formatting time...i think

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;

Tags (1)
1 Solution

Accepted Solutions
alexpanjhc
Valued Contributor

Re: Problem formatting time...i think

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.

6 Replies
chematos
Valued Contributor II

Re: Problem formatting time...i think

Try with

Inner join (TEST_TIMESTAMP)

instead Left Join


alexpanjhc
Valued Contributor

Re: Problem formatting time...i think

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
Valued Contributor II

Re: Problem formatting time...i think

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
Contributor

Re: Problem formatting time...i think

I update my post with the excel file.

Thanks for your help

alexpanjhc
Valued Contributor

Re: Problem formatting time...i think

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
Contributor

Re: Problem formatting time...i think

Thanks for your quick help.

It was just what I was looking for.

Thanks

Community Browser