Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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..
Hi,
Did you checked the logic which I provided?
Regards,
jagan.
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
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;
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;
Check this out -
Hi,
Thanks for your support, got it.
Regards,
SubbaReddy