13 Replies Latest reply: Mar 1, 2016 1:50 AM by jagan mohan rao appala RSS

    Logfile Dashboard Report

    Subba Reddy

      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 mohan rao appala

          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 Periyasamy

            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..

            Capture.JPG

            • Re: Logfile Dashboard Report
              Digvijay Singh

              Check this out -

              Capture.JPG