topic Re: Date Format in QlikView

Date Format

anuradhaa — Wed, 26 Sep 2012 04:46:50 GMT

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

Re: Date Format

Wed, 26 Sep 2012 05:14:57 GMT

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

Re: Date Format

anuradhaa — Wed, 26 Sep 2012 05:39:39 GMT

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

Re: Date Format

Wed, 26 Sep 2012 05:46:11 GMT

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

Re: Date Format

anuradhaa — Wed, 26 Sep 2012 05:56:08 GMT

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

Re: Date Format

jonathandienst — Wed, 26 Sep 2012 06:15:53 GMT

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

Re: Date Format

anuradhaa — Wed, 26 Sep 2012 06:21:25 GMT

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

Re: Date Format

hic — Wed, 26 Sep 2012 06:28:15 GMT

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

Re: Date Format

anuradhaa — Wed, 26 Sep 2012 06:36:02 GMT

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

.......

Re: Date Format

Wed, 26 Sep 2012 06:39:17 GMT

Hi,

May I ask for your sample data.

Thanks,

Janzen

Re: Date Format

anuradhaa — Wed, 26 Sep 2012 06:39:45 GMT

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

Re: Date Format

anuradhaa — Wed, 26 Sep 2012 06:43:04 GMT

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

Re: Date Format

anuradhaa — Wed, 26 Sep 2012 07:11:26 GMT

Hi Please find below excel sheet.

Hope it will help you,

waitting your kind reply

Thanks

Re: Date Format

hic — Wed, 26 Sep 2012 07:26:55 GMT

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

Re: Date Format

jonathandienst — Wed, 26 Sep 2012 07:38:21 GMT

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

Re: Date Format

hic — Wed, 26 Sep 2012 07:44:14 GMT

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

Re: Date Format

Wed, 26 Sep 2012 08:04:25 GMT

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

Re: Date Format

anuradhaa — Wed, 26 Sep 2012 08:12:09 GMT

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

Re: Date Format

hic — Wed, 26 Sep 2012 08:14:14 GMT

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

Re: Date Format

jonathandienst — Wed, 26 Sep 2012 08:16:13 GMT

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