Discussion Board for collaboration on QlikView Scripting.
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.
@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
LB179_20120924_194125.xls(biff, no labels, table is Sheet1$);
load *, len(my_Timestamp) as my_Timestamp_length Resident TEMP_TEST_TIMESTAMP;
drop table TEMP_TEST_TIMESTAMP;
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)
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;
Go to 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
Inner join (TEST_TIMESTAMP)
instead Left Join
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
* INLINE [my_Timestamp_original, my_Timestamp, my_date, my_time2012-03-21 00:53:07, 2012-03-21 00:53:07, 2012-03-21, 00:53:072012-03-21 06:01:01, 2012-03-21 06:01:01, 2012-03-21, 06:01:012012-03-21 10:00:01, 2012-03-21 10:00:01, 2012-03-21, 10:00:012012-03-21 18:00:01, 2012-03-21 18:00:01, 2012-03-21, 18:00:012012-03-21 23:00:01, 2012-03-21 23:00:01, 2012-03-21, 23:00:01]
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
I update my post with the excel file.
Thanks for your help
Thanks for your quick help.
It was just what I was looking for.