topic Re: Logfile Dashboard Report in QlikView

Logfile Dashboard Report

Tue, 01 Mar 2016 02:32:41 GMT

Hi,

PFA excel file with sample data, from this data i want to show only latest record (marked in yellow colour).

Thanks in advance.

Sub2u444.

Re: Logfile Dashboard Report

jagan — Tue, 01 Mar 2016 02:50:03 GMT

Hi,

Try like this

Data:

LOAD

RowNo() AS SerialNo

INLINE [

Timestamp, TaskName, Status, StartTime, StopTime, Duration

20160223T050024.225-0800, Japan, FinishedSuccess, 20160223T050000.584000-0800, 20160223T050023.350070-0800, 22766

20160223T070028.130-0800, Japan, FinishedSuccess, 20160223T070001.021000-0800, 20160223T070027.287022-0800, 26266

20160223T090024.495-0800, Japan, FinishedSuccess, 20160223T090000.603000-0800, 20160223T090023.759937-0800, 23156

20160223T110023.494-0800, Japan, FinishedSuccess, 20160223T110001.120000-0800, 20160223T110022.729202-0800, 21609

20160223T130026.273-0800, Japan, FinishedSuccess, 20160223T130000.538000-0800, 20160223T130025.913689-0800, 25375

20160223T150028.306-0800, Japan, FinishedSuccess, 20160223T150000.741000-0800, 20160223T150027.384819-0800, 26643

20160223T030036.621-0800, Micron, FinishedSuccess, 20160223T030003.882000-0800, 20160223T030035.090719-0800, 31208

20160223T050027.928-0800, Micron, FinishedSuccess, 20160223T050003.740000-0800, 20160223T050027.725071-0800, 23985

20160223T070033.161-0800, Micron, FinishedSuccess, 20160223T070005.755000-0800, 20160223T070032.130728-0800, 26375

20160223T090031.186-0800, Micron, FinishedSuccess, 20160223T090003.650000-0800, 20160223T090028.965635-0800, 25315

20160223T110027.307-0800, Micron, FinishedSuccess, 20160223T110003.932000-0800, 20160223T110026.713493-0800, 22781

20160223T130029.898-0800, Micron, FinishedSuccess, 20160223T130006.476000-0800, 20160223T130029.351124-0800, 22875

20160223T150031.934-0800, Micron, FinishedSuccess, 20160223T150006.822000-0800, 20160223T150031.528380-0800, 24706

20160223T040021.304-0800, USA, FinishedSuccess, 20160223T040002.288000-0800, 20160223T040020.944961-0800, 18656

20160223T060023.619-0800, USA, FinishedSuccess, 20160223T060002.510000-0800, 20160223T060023.166318-0800, 20656

20160223T080023.337-0800, USA, FinishedSuccess, 20160223T080003.151000-0800, 20160223T080022.931660-0800, 19780

20160223T100022.624-0800, USA, FinishedSuccess, 20160223T100002.468000-0800, 20160223T100022.203044-0800, 19735

20160223T120022.672-0800, USA, FinishedSuccess, 20160223T120002.376000-0800, 20160223T120022.344856-0800, 19968

20160223T140028.841-0800, USA, FinishedSuccess, 20160223T140003.451000-0800, 20160223T140028.341565-0800, 24890

];

INNER JOIN(Data)

LOAD

TaskName,

Max(SerialNo) AS SerialNo

RESIDENT Data

GROUP BY TaskName;

Regards,

Jagan.

Re: Logfile Dashboard Report

settu_periasamy — Tue, 01 Mar 2016 03:17:52 GMT

Hi,

Try this..

Directory;
T1:
LOAD 
  TimeStamp(TimeStamp#(Date#(SubField(Timestamp,'T',1),'YYYYMMDD')&Time(TextBetween(Timestamp, 'T','-')),'YYYYMMDDh:mm:ss TT')) as Timestamp,
     TaskName, 
     Status, 
     TimeStamp(TimeStamp#(Date#(SubField(StartTime,'T',1),'YYYYMMDD')&Time(TextBetween(StartTime, 'T','-')),'YYYYMMDDh:mm:ss TT')) as StartTime,
     TimeStamp(TimeStamp#(Date#(SubField(StopTime,'T',1),'YYYYMMDD')&Time(TextBetween(StopTime, 'T','-')),'YYYYMMDDh:mm:ss TT')) as StopTime,       
     Duration
FROM
Sample.xlsx
(ooxml, embedded labels, table is Sheet1);


Inner Join
LOAD TaskName,
  Max(Timestamp) as Timestamp Resident T1 Group by TaskName;

Edit:

Instead of Left join, Inner join used..

Re: Logfile Dashboard Report

Tue, 01 Mar 2016 03:46:25 GMT

Hi,

Its loading, but not showing the data. I don't know where i did the mistake.

Sub2u444‌

Re: Logfile Dashboard Report

jagan — Tue, 01 Mar 2016 03:50:21 GMT

Hi,

Did you checked the logic which I provided?

Regards,

jagan.

Re: Logfile Dashboard Report

Tue, 01 Mar 2016 03:57:51 GMT

Hi Jagan,

This is not permanent log file, keep on the values will be changed. I hope in your shared script directly hard coded in inline load. can you please help.

Sub2u444

Re: Logfile Dashboard Report

jagan — Tue, 01 Mar 2016 04:05:02 GMT

Hi,

For sample purpose I used Inline load, instead you just use

Data:

LOAD

RowNo() AS SerialNo

FROM Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

INNER JOIN(Data)

LOAD

TaskName,

Max(SerialNo) AS SerialNo

RESIDENT Data

GROUP BY TaskName;

Re: Logfile Dashboard Report

settu_periasamy — Tue, 01 Mar 2016 04:40:09 GMT

Hi,

It's worked for me.. My version 11.2 SR 9

May be try this..

T1:  
LOAD   Timestamp(Timestamp#(PurgeChar(SubField(Timestamp,'.',1),'T'),'YYYYMMDDhhmmss')) as Timestamp,  
      TaskName,   
  Status,   
      Timestamp(Timestamp#(PurgeChar(SubField(StartTime,'.',1),'T'),'YYYYMMDDhhmmss')) as StartTime,
     Timestamp(Timestamp#(PurgeChar(SubField(StopTime,'.',1),'T'),'YYYYMMDDhhmmss')) as StopTime,      
     Duration  
FROM  
Sample.xlsx
(ooxml, embedded labels, table is Sheet1);  


Inner Join  
LOAD TaskName,  
  Max(Timestamp) as Timestamp Resident T1 Group by TaskName;