topic Re: Dateformat in QlikView

Dateformat

Thu, 14 Mar 2013 06:30:52 GMT

Hi,

Am having date format as attached in the file. I want it to be converted into yyyy-mm-dd HH.MM.SS format.

Regards,

Tejavardhan

Re: Dateformat

er_mohit — Thu, 14 Mar 2013 06:35:24 GMT

DATE(DATE#(FIELDNAME,'DD-MM-YYYY hh:mm:ss'),'YYYY-MM-DD hh:mm:ss') as DATE

here in first its your field nformat and then write that format which you want....

Re: Dateformat

Thu, 14 Mar 2013 06:40:31 GMT

try this

Date(Date#(fieldname,'ddd MMM DD hh:mm:ss YYYY'),'YYYY-MM-DD hh:mm:ss')

Re: Dateformat

Thu, 14 Mar 2013 06:45:35 GMT

Hi,

Its not Working.

Re: Dateformat

Thu, 14 Mar 2013 06:49:58 GMT

hi,

Am using this syntax

Date(Date#[created_at],'EEE MMM dd HH:mm:ss ZZZ YYYY'),'yyyy-mm-dd') as [created_date]

but its not working.

What exactlty is the denotion for the format dat i have attached in the previous document.

Regards,

Tejavardhan

Re: Dateformat

Thu, 14 Mar 2013 06:58:35 GMT

IN YPUR SCRIPT

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;

Date(Date#(fieldname,'WWWW MMM DD hh:mm:ss YYYY'),'YYYY-MM-DD hh:mm:ss')

Re: Dateformat

sushil353 — Thu, 14 Mar 2013 07:00:35 GMT

Hi try this..

Please find attached

Re: Dateformat

Thu, 14 Mar 2013 07:05:36 GMT

Am using personnel edition i cant open .qvw format files. can u help me in other way

Regards,

Tejavardhan

Re: Dateformat

sushil353 — Thu, 14 Mar 2013 07:07:29 GMT

Try script:

tab1:

LOAD * Inline

[

datefield

Fri Nov 30 23:01:26 +0000 2012

Fri Nov 30 23:01:29 +0000 2012

Fri Nov 30 23:01:31 +0000 2012

Fri Nov 30 23:01:34 +0000 2012

Fri Nov 30 23:01:36 +0000 2012

Fri Nov 30 23:01:37 +0000 2012

Fri Nov 30 23:01:38 +0000 2012

];

Temp1:

LOAD

mid(datefield,5,15) as first,

Right(datefield,4) as second

resident tab1;

Temp2:

LOAD

first &' '&second as test

Resident Temp1;

final:

LOAD

Date(Date#(test,'MMM DD hh:mm:ss YYYY'),'YYYY-MM-DD hh:mm:ss') as date_t

Resident Temp2;

DROP Tables Temp1,Temp2;

Re: Dateformat

Thu, 14 Mar 2013 07:09:19 GMT

hi sushil,

Is there any other way for this because am having 1lakh rows i cant create inline function for all those rows that is the problem.

Regards,

Tejavardhan

Re: Dateformat

sushil353 — Thu, 14 Mar 2013 07:11:47 GMT

inline load is for testing purpose you can directly use your date field..

Let me know your table structure

Re: Dateformat

Thu, 14 Mar 2013 07:26:13 GMT

Hi,

Please Find The Attached Sample File, Definitely it will helpful for You.

Thanks & Regards,

K.J.Nirmal Raj.

Re: Dateformat

Thu, 14 Mar 2013 08:01:27 GMT

Hi nirmal,

Am using personnel edition i cant open the file that you have sent.

Regards,

Tejavardhan

Re: Dateformat

er_mohit — Thu, 14 Mar 2013 08:11:14 GMT

try this

here F1=Fri Nov 30 23:01:22 +0000 2012

in script

date( date#(

subfield(F1,' ',2)

&'-'& keepchar(subfield(F1,' ',3),'0123456789')

&'-'& keepchar(subfield(F1,' ',6),'0123456789')

&'-'& subfield(F1,' ',4)

,'MMM-DD-YYYY-hh:mm:ss') ,'YYYY-MM-DD hh:mm:ss')

as NEWDATEFIELD1

Re: Dateformat

Thu, 14 Mar 2013 08:39:39 GMT

Hi teja,

Test:

LOAD Date,
     KeepChar(left(mid(Date,5),3),Date) as Month,
     mid(Date,Index(Date,' ',3)-2,2) as Date1,
     mid(Date,Index(Date,' ',5)-14,9) as Time,
     mid(Date,Index(Date,' ',5),5) as Year,
     Sales
FROM
G:\Test1.xlsx
(ooxml, embedded labels, table is Sheet1);

Test2:

Load *,
Date1&'/'&Month&'/'&trim(Year)&' '&(Time) as Timestamp
Resident Test;
DROP Table Test;

PFA of Result Snapshot.

Re: Dateformat

sushil353 — Thu, 14 Mar 2013 09:05:12 GMT

Hi this is not in date format... it is returning as string

Re: Dateformat

Thu, 14 Mar 2013 09:55:08 GMT

Hi,

Its working well with six records that we put in inline function. The column name of dat is created_at if we put as

tab1:

load * inline

[

created_at

];

and then the code u mentioned its throwing error.

Regards,

Tejavardhan

Re: Dateformat

Thu, 14 Mar 2013 10:21:23 GMT

Hi teja,

Is this Working Properly,

Regards,

Nirmal.

Re: Dateformat

Thu, 14 Mar 2013 11:38:44 GMT

hi nirmal,

yeah its working well. thanks

Regards,

tejavardhan.

Re: Dateformat

Thu, 14 Mar 2013 11:42:56 GMT

Its ok teja