Date Format

[anuradhaa]

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

[anuradhaa]

here TIME MEANS field name wich contains date and time, the data type is timestamp

[anuradhaa]

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

[anuradhaa]

Hi Please find below excel sheet.

Hope it will help you,

waitting your kind reply

Thanks

[hic]

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:

Blog on dates

White paper on dates

HIC

[jonathandienst · Accepted solution]

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

[hic]

Jonathan: There is a comma missing after the Year function ....

[#8]

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

[anuradhaa]

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

[hic]

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

[jonathandienst]

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