Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Data set which has "Time" as "17-SEP-12 01.08.47.530179000 AM"
So i need to divide taht time to,
Year
Month
Day
Hours
minutes
Please help.
At least tell me how to devide it in to Year,Month,Day
Thanks
Hi
Integrated with your script, the code becomes:
LOAD *,
Month(Date) As Month,
Year(Date) As Year,
Day(Date) as Day,
Hour(Date) As Hour,
Minute(Date) As Minute;
LOAD Date(Date#(TIME, 'DD-MMM-YY hh.mm.ss.fffffffff tt')) As Date,
MACHINE_NAME,
IP,
CMD,
HEADER,
LABLE,
STATUS,
TIME,
if(STATUS='GREEN',1,0) As UP
FROM
.......
Regards
Jonathan
Missing comma after year fixed.
Missing * in first LOAD fixed
Hi Anuradhaa,
Try this format.
'20' & mid(Time,8,2) as Year,
mid(Time,4,3) as Month,
mid(Time,1,2) as Day,
mid(Time,11,2) as Hour,
mid(Time,14,2) as Minute
Best Regards,
Janzen
Not works.
but i have done some modifications to your code then Hour and minute is ok, but others have problem.
This is my modified code ,
mid(Time,1,2) as Hour,
mid(Time,4,2) as Minute,
I think this fuction ignores first part , take the data after the space
Thanks
Hi,
If you use your data as string, It should display proper values. Using your modified code, mid(Time,1,2) will display '17' which I thought is a day, mid(Time,4,2) will display 'SE'.
May I know what is the format of your data?
Thanks,
Janzen
In the Data Base it appiers as "TIMESTAMP(6)"
But i have expot the data to excel and use that excel in qlikview,
The data type in excel is "General"
Thanks
Hi
First convert the value to a date/time and use the standard QV functions for the date parts. In a load statement you could do this (assuming Time is the name of the timestamp field):
LOAD Month(Date) As Month,
Year(Date) As Year
Day(Date) as Day,
Hour(Date) As Hour,
Minute(Date) As Minute;
LOAD Date(Date#(Time, 'DD-MMM-YY hh.mm.ss.fffffffff tt')) As Date,
...
...
FROM ...;
Hope that helps
Jonathan
where do i need to change the data type?
DB or excel?
Is there a way to do it by using data type as "Time Stamp" in excel.
Thanks
You do not need to change the data type. To QlikView this is a string that needs to be interpreted. Just use the time interpretation function that Jonathan suggests.
HIC
Thanks,
But i was confused, have problems in put that code
here is my code, please help me to put that in side here
LOAD MACHINE_NAME,
IP,
CMD,
HEADER,
LABLE,
STATUS,
TIME,
if(STATUS='GREEN',1,0)as UP
FROM
.......
Hi,
May I ask for your sample data.
Thanks,
Janzen