Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Parsing the timestamps in the audit logs

I'm trying to load data from the Audit_....log file qlikview generates, such as:

Server Started Timestamp Document Type User Message

20171122T084730.000Z 20171205T081111.000Z D:/QlikView/Documents/Live/sales/sales.qvw Action AQ\Jgorman action(32)[TypeKey, Contract, ] Document\BU198_079632301 4023

20171122T084730.000Z 20171205T081134.000Z D:/QlikView/Documents/Live/sales/sales.qvw Sheet AQ\Jgorman Activated sheet Document\SH26_636756900 4023

20171122T084730.000Z 20171205T081147.000Z D:/QlikView/Documents/Live/sales/sales.qvw Action AQ\Jgorman action(4)[$Field, ] Document\BU484_955942433 4023

20171122T084730.000Z 20171205T081147.000Z D:/QlikView/Documents/Live/sales/sales.qvw Selection AQ\Jgorman Clear All 4023


I've tried many, many, different formulae, but cannot work out how to get the timestamp into a proper date format so I can do a mapping to my time dimension:


What I have so far is:


LOAD ApplyMap('TimeMap',Floor(Timestamp#(Timestamp, 'yyyyMMddThhmmss.fffZ'))) AS DateTimeKey,

'Usage' AS RecTypeKey,

ConvertToLocalTime(Timestamp) AS LocalTime,

Num(Timestamp#(Timestamp)) AS NoFormat,

Num(Timestamp#(Timestamp, 'yyyyMMddThhmmss.fffZ')) AS Format,

'Usage' AS TypeKey,

     Timestamp,

     Document,

     Type,

     ApplyMap('UserMap',Upper(User)) AS UserKey,

     Message

FROM

[\\Qlikview\d$\QlikView\Documents\Live\Logs\Audit_QLIKVIEW.log]

(txt, utf8, embedded labels, delimiter is '\t', msq);


However, I just get a '-' showing for the DateTimeKey, LocalTime, NoFormat and Format fields.


What am I doing wrong?

1 Solution

Accepted Solutions
Highlighted

May be try this

=TimeStamp(Timestamp#(PurgeChar(Timestamp, 'TZ'), 'YYYYMMDDhhmmss.fff'))

View solution in original post

3 Replies
Highlighted

May be try this

=TimeStamp(Timestamp#(PurgeChar(Timestamp, 'TZ'), 'YYYYMMDDhhmmss.fff'))

View solution in original post

Highlighted
Contributor III
Contributor III

That's done it, thanks. Guess QlikView doesn't like literals in format strings for some reason!

Highlighted

I have seen it working in somewhere, but it didn't work for me as well... that's why I thought it might make sense to remove T and Z...