3 Replies Latest reply: Dec 5, 2017 9:56 AM by Sunny Talwar RSS

    Parsing the timestamps in the audit logs

    George Duckett

      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,




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




      (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?