Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Timestamp Problem

Hi,

I have some problem with the timestamp function that I am using. I have two excel sheets for the month of Jan 2016 and Dec 2015. The date column (in my case, "Incident created Date and Time") is a timestamp in both the sheets. I need to retrieve month and year from it. The timestamp formula that I have used works fine with Jan 2016 data but not with Dec 2015 data. I am attaching the qvf and the excel sheets. Please help

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Timestamp Problem

Your Dec data seems to use a different white space character between hh:mm and tt than in Jan data:

timestamp#( Replace("Incident created Date and Time",chr(160),' '), 'MM/DD/YYYY hh:mm tt') as  "Incident created Date and Time",

So either use replace() function, correct your source data, or use left() to only read in the date part.

5 Replies
vijitbhargav
New Contributor III

Re: Timestamp Problem

Hello Aishwarya,

No sure whether you've found what you were looking for, but following expression can be tried:

=Date(Timestamp(Timestamp#([Incident created Date and Time],'MM/DD/YYYY hh:mm tt'),'MM/DD/YYYY hh:mm tt'),'MMM-YYYY')

Regards,

Vijit

Not applicable

Re: Timestamp Problem

Hi Vijit,

Thanks for the reply. I tried this but even this formula works only for Jan 2016 data and not for Dec 2015 data.

Re: Timestamp Problem

Hi,

Try this script.

LOAD [Incidents #],

     ([Incident created Date and Time]),

     [Incident Resolved Date and Time],

     [Incident Closed Date],

     Date(Date#(left([Incident created Date and Time],10),'MM/DD/YYYY')) as Date,

     Month(Date(Date#(left([Incident created Date and Time],10),'MM/DD/YYYY'))) as Month

FROM

[RCA Report Dummy.xlsx]

(ooxml, embedded labels, table is [Dec-15]);

LOAD [Incidents #],

     [Incident created Date and Time],

     [Incident Resolved Date and Time],

     [Incident Closed Date],

     Date(Date#(left([Incident created Date and Time],10),'MM/DD/YYYY')) as Date,

     Month(Date(Date#(left([Incident created Date and Time],10),'MM/DD/YYYY'))) as Month

FROM

[RCA Report Dummy.xlsx]

(ooxml, embedded labels, table is [Jan-16]);

Regards,

Kaushik Solanki

Highlighted
MVP
MVP

Re: Timestamp Problem

Your Dec data seems to use a different white space character between hh:mm and tt than in Jan data:

timestamp#( Replace("Incident created Date and Time",chr(160),' '), 'MM/DD/YYYY hh:mm tt') as  "Incident created Date and Time",

So either use replace() function, correct your source data, or use left() to only read in the date part.

Not applicable

Re: Timestamp Problem

Thanks for the reply Kaushik. It worked fine with the replace function. Thank you Swuehl.