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
here TIME MEANS field name wich contains date and time, the data type is timestamp
MACHINE_NAME | IP | CMD | HEADER | LABLE | STATUS | TIME |
A | 34 | port1 | PORT | port1 | RED | 17-SEP-12 01.08.14.444784000 AM |
A | 56 | port3 | PORT | port2 | RED | 17-SEP-12 01.08.17.501740000 AM |
B | 14 | port4 | PORT | port5 | RED | 17-SEP-12 01.08.20.567218000 AM |
C | 78 | port5 | PORT | port6 | RED | 17-SEP-12 01.08.23.609672000 AM |
This is a sample of my data set,
in db the data type of Time is Time stamp,
in exported excel it is general
Thanks
Hi Please find below excel sheet.
Hope it will help you,
waitting your kind reply
Thanks
If the data type in the database is Timestamp, then you will get it as timestamp automatically in QlikView if you use ODBC or OLEDB.
In the above export to Excel, the field has the type "General" which means string. A date/timestamp would be right-aligned and contain a number ~40000. So the export has in a way failed, since Excel doesn't recognize that it is a timestamp. Anyway, when you load this into QlikView, you should use interpretation functions, like Jonathan suggests.
See more on:
HIC
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
Jonathan: There is a comma missing after the Year function ....
Hi,
Please find the script pasted.
Mapy:
Mapping LOAD * INLINE
[ month_str, month_num
JAN, 01
FEB, 02
MAR, 03
APR, 04
MAY, 05
JUN, 06
JUL, 07
AUG, 08
SEP, 09
OCT, 10
NOV, 11
DEC, 12
];
Test:
LOAD Machine_Name,
IP,
HEADER,
LABLE,
STATUS,
STATUS1,
TIME,
MapSubString('Mapy',TIME) as t2
FROM Date_formate.xlsx
(ooxml, embedded labels, table is Sheet1);
Test2:
LOAD Machine_Name,
date(MakeDate(num('20' & Mid(t2, 7, 2), '0000'), Mid(t2, 4, 2), Left(t2, 2)), 'DD-MM-YYYY') as date,
MakeTime(Mid(t2, 10, 2), Mid(t2, 13, 2), Mid(t2, 16, 2)) as time
Resident Test;
HTH
Regards,
Shubhu
Looks good
Date is seperated,
But it only loads the date,
where is my other data
MACHINE_NAME,
IP,
CMD,
HEADER,
LABLE,
STATUS,
TIME,
Thanks
Shubhu
Using a MapSubString to convert a month name to a month number will work, but it is not necessary. This functionality already exists inside the interpretation functions (Time#(), Timestamp#(), Date#(), etc.).
HIC
Henric Cronström wrote:
Jonathan: There is a comma missing after the Year function ....
Fixed in the post. Thanks Hendric - thats what happens when you post untested code
Jonathan