topic Re: Date conversion in QlikView

Date conversion

brindlogcool — Fri, 27 Mar 2015 15:00:30 GMT

I have a date field which is always showing as Text format even after using interpretation function Date#. The data source is Txt file.

I tried Date#()

I tried floor with Date#()

Still no use. But i had another txt file which has date function and it is working perfectly fine using the interpretation function Date#.

Not able to identify root cause for the issue. And more concern is when the function is working with one text file and not working with other,

i had through HIC posts related to date filed but nothing works. Is it anything related to encoding format.

Re: Date conversion

Alejandro_Hernández — Fri, 27 Mar 2015 15:01:22 GMT

Could you post an example of your date values?

Re: Date conversion

Anonymous — Fri, 27 Mar 2015 15:10:38 GMT

Hi Brindlogcool,

Try this:

Date#(Num('field')) if your data looks like YYYYMMDD

date(date#(DateField,'YYYY-MM-DD')) if your DateField looks like YYYY-MM-DD

I hope this will help you

Re: Date conversion

lawrenceiow — Fri, 27 Mar 2015 15:21:52 GMT

as @Alejandro_Hernandez says, tt would be handy if you provided some examples of the dates as they appear in your txt file.

You've not mentioned it specifically but have you tried using date(date#())?

So if your DateField looks like 2015-03-27 you would use

date(date#(DateField,'YYYY-MM-DD'))

Re: Date conversion

brindlogcool — Fri, 27 Mar 2015 16:14:56 GMT

Attached the file for the reference. Had deleted all the details except the date

Re: Date conversion

lawrenceiow — Fri, 27 Mar 2015 16:34:38 GMT

So, I presume you used date(date#(mid(DateField,22,10),'MM/DD/YYYY')) and that did not work?

Re: Date conversion

brindlogcool — Fri, 27 Mar 2015 16:56:01 GMT

Thanks. Yes it didn't work. I checked the file format is ANSI. I am not sure do i need to do anything with this setting.

Re: Date conversion

maxgro — Fri, 27 Mar 2015 18:23:07 GMT

load

makedate(Right(row,4), left(row,2), mid(row,4,2)) as d;

LOAD

left(KeepChar([@1:n],'/0123456789:'),10) as row

FROM

[03-25-2015.txt]

(fix, codepage is 1252)

where len(trim([@1:n]))>0;

another one

load

date(Date#(row, 'MM/DD/YYYYhh:mm:ss'), 'MM-DD-YYYY hhmmss') as d2

;LOAD

left(KeepChar([@1:n],'/0123456789:'),18) as row

FROM

[03-25-2015.txt]

(fix, codepage is 1252)

where len(trim([@1:n]))>0;

Re: Date conversion

pokassov — Fri, 27 Mar 2015 18:46:10 GMT

Can you check this code?

I copied the whole text file in "inline" load.

both fields work correctly

t1:

load * Inline [

textdata

-------Start of log-- 03/25/2015 13:16:13 --------

-------Start of log-- 03/25/2015 17:10:42 --------

-------Start of log-- 03/25/2015 18:54:32 --------

];

t1:

LOAD

date(date#(data,'MM/DD/YYYY hh:mm:ss')) as data,

Timestamp(Timestamp#(data,'MM/DD/YYYY hh:mm:ss')) as datatime;

LOAD

mid(textdata,23,19) as data

Resident

t1;

The result (regarding my settings):

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';

Re: Date conversion

brindlogcool — Fri, 27 Mar 2015 19:30:45 GMT

Are you able to get the same results from the attached text file without inline table?

Re: Date conversion

pokassov — Fri, 27 Mar 2015 19:34:52 GMT

Sure

t1:

LOAD @1 as textdata

FROM

(txt, codepage is 1251, no labels, delimiter is '\t', msq);

t2:

LOAD

date(date#(data,'MM/DD/YYYY hh:mm:ss')) as data,

Timestamp(Timestamp#(data,'MM/DD/YYYY hh:mm:ss')) as datatime;

LOAD

mid(textdata,23,19) as data

Resident

t1;

Result's the same. OK

Did your try Inline?