topic Timestamp conversion in QlikView

Timestamp conversion

Mon, 12 Jan 2015 07:19:44 GMT

Hi Folks,

I am loading a excel file , this have the time stamp data as 'DD/MM/YYYY HH:MM:SS AM' .

I am not able to convert this into date and extract month,year ..

the expression which i am uisng is as follows

Date(floor([Completed Time]))

Thanks in Advance...

Regards

Abdul

Re: Timestamp conversion

anbu1984 — Mon, 12 Jan 2015 07:24:53 GMT

Load Date(TS,'DD/MM/YYYY') As Dt,Year(TS) As Yr,Month(TS) As Mth;

Load Timestamp#(TS,'DD/MM/YYYY hh:mm:ss TT') As TS Inline [

01/01/2015 01:00:00 AM

01/02/2015 01:00:00 AM ];

Re: Timestamp conversion

Colin-Albert — Mon, 12 Jan 2015 07:33:57 GMT

Your expression needs to include Date#() to convert the text data to a date.

Date(floor(Date#([completed Time], 'DD/MM/YYYY hh.mm.ss tt')))

QlikView Date fields

Re: Timestamp conversion

Anonymous — Mon, 12 Jan 2015 07:35:43 GMT

Hi Abdul,

first check data format in your excel if it is datetime format or general format .

If it is general format convert it to datetime format and then try.

if it is in datetime format (in excel) then Anbu solution should work for you .

Re: Timestamp conversion

Mon, 12 Jan 2015 07:36:49 GMT

Thanks for the reply .

I am still not able get the date and month , i have placed the above load statement in the script .

it gives me a blank column - no data in it,

Thanks in Advance..

Re: Timestamp conversion

MarcoWedel — Mon, 12 Jan 2015 07:44:33 GMT

Hi,

try

LOAD *,
          DayName([Completed Time]) as Date,
          Month([Completed Time]) as Month,
          Year([Completed Time]) as Year;
LOAD Timestamp#([Completed Time], ''DD/MM/YYYY hh:mm:ss TT'') as [Completed Time]
FROM yourSource;

hope this helps

regards

Marco

Re: Timestamp conversion

anbu1984 — Mon, 12 Jan 2015 07:49:56 GMT

Can you post sample data

Re: Timestamp conversion

Mon, 12 Jan 2015 07:58:43 GMT

Please find the sample data file.

Thanks in Advance...

Re: Timestamp conversion

Anonymous — Mon, 12 Jan 2015 08:14:02 GMT

see your data is not in proper datetime format in CSV.

Re: Timestamp conversion

anbu1984 — Mon, 12 Jan 2015 08:15:31 GMT

Load Year([Created Time]),Month([Created Time]),Date(Floor([Created Time]),'M/D/YYYY');

LOAD Group,

[Request ID],

Department,

Category,

Subcategory,

Item,

Urgency,

Impact,

[Request Type],

[Approval Status],

[Request Closure Code],

[Request Closure Comments],

Priority,

[Request Status],

[Pending Status],

[Created Time] As [Created Time1],

Timestamp#([Created Time],'DD/MM/YYYY hh:mm') As [Created Time],

[Responded Date],

[DueBy Time],

[Completed Time],

[Time Elapsed],

[Overdue Status]

FROM

[Tickets.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

Re: Timestamp conversion

kiranmanoharrode — Mon, 12 Jan 2015 08:18:44 GMT

Hi Abdul,

just use below script, I think it will solve the issue.

Date(floor(Num([Completed Time])))

Regards,

Kiran

Re: Timestamp conversion

its_anandrjs — Mon, 12 Jan 2015 08:32:47 GMT

Hi,

Use Alt function it because you have different date formats in the field and also it in not in the timestamp format try below load script for the solution and convert Year and Month also same.

Source:

LOAD *,Year([Completed Time New]) as Year, Month([Completed Time New]) as Month;
LOAD
[Completed Time],
Alt(Timestamp(Timestamp#([Completed Time],'MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm'),
Timestamp(Timestamp#([Completed Time],'DD/MM/YYYY hh:mm TT'),'DD/MM/YYYY hh:mm TT'),
Timestamp(Timestamp#([Completed Time],'MM/DD/YYYY hh:mm TT'),'MM/DD/YYYY hh:mm TT'),
Timestamp(Timestamp#([Completed Time],'M/D/YYYY hh:mm TT'),'M/D/YYYY hh:mm TT'),'MM/DD/YYYY hh:mm') as [Completed Time New]
FROM
Tickets.csv
(txt, codepageis 1252, embeddedlabels, delimiteris ',', msq, headeris 1 lines);

And fields that you get

Regards

Anand

Re: Timestamp conversion

Mon, 12 Jan 2015 08:48:20 GMT

Thanks Anbu .. its working fine now.

Re: Timestamp conversion

Mon, 12 Jan 2015 08:49:12 GMT

Thanks Anand. it helped me to resolve in case of multiple formats in the same column..