Need to get the latest timestamp - how to filter?

[dmohanty]

Hi All,,

For example, I have multiple timestamps (dates and time 2 fields) for a single ID. Like this below:

ID	Date	Time
100	20141002	1205
100	20141003	0612
100	20141006	0607

Now I want the latest Date and Time from this, not the old. How to edit the script. The data would come like this now:

ID	Date	Time
100	20141006	0607

[jagan]

Hi,

Try like this

LOAD

*

FROM DataSource

WHERE Peek('ID') <> ID

ORDER BY ID, Date DESC, Time DESC;

Hope this helps you.

Regards,

Jagan.

[dmohanty]

Hi Jagan,

I am getting garbage after statement error. Any suggestions please

[ashfaq_haseeb]

Hi,

Try like this

Temp:

load * Inline

[

ID,Date,Time

100,20141002,1205

100,20141003,0612

100,20141006,0607

];

NoConcatenate

Main:

Load

ID,

Date,

Time

Resident Temp

WHERE Peek('ID') <> ID

ORDER BY ID, Date DESC, Time DESC;

drop Table Temp;

Regards

ASHFAQ

[jagan]

Hi,

What is your data source?  Try like this

Temp:

LOAD

*

FROM DataSource;

Data:

Noconcatenate

LOAD

*

RESIDENT Temp

WHERE Peek('ID') <> ID

ORDER BY ID, Date DESC, Time DESC;

Drop Table Temp;

Hope this helps you.

Regards,

Jagan.

[suresh_rawat]

Hi,

Use this script, it will work surely for your query.

LOAD ID,
     Date,
     Time
    
FROM
Table1;

Store This Data in a QVD and drop the table after rename the table as Table1.qvd

Then USe this Script

LOAD ID as newID
  FROM
Table1;

NoConcatenate

Data:

LOAD ID,
     Date,
     Time
     FROM
Table1;

LOAD ID,
     Date,
     Time
    
FROM
Table1.qvd;

For any Doubt please ask.

Regards

Suresh Rawat

[its_anandrjs]

Then make some changes in your load script

tmp: //Source Data

LOAD ID, Date, Time, ID&Date as Key;

LOAD * INLINE [

    ID, Date, Time

    100, 20141002, 1205

    100, 20141003, 0612

    100, 20141006, 0607

];

tmpFinal:

LOAD

ID, Max(Date) as MaxDate

Resident tmp Group By ID;

NoConcatenate

tmpMaxRow:

LOAD

ID as MaxID, MaxDate, ID&MaxDate as Key

Resident tmpFinal;

DROP Table tmpFinal;

Right Join

LOAD Key,Time as MaxTime Resident tmp;

NoConcatenate

MaxRow: //Final Max Row you get

LOAD

Key,MaxID,MaxTime,MaxDate

Resident tmpMaxRow Where Len(MaxID)>0;

DROP Table tmpMaxRow;

This works for all source data that you have and you get table like

Regards,

Anand

[#8]

Try like:

test:

LOAD ID,Date(Date#(Date,'YYYYMMDD')) as Date_t, Time(Time#(Time,'hhmm')) as Time_t INLINE [

    ID, Date, Time

    100, 20141002, 1205

    100, 20141003, 0612

    100, 20141006, 0607

];

test_h:

Load

ID,

Date_t,

Time_t,

Date_t & ' '& Time_t as Datetime

Resident test;

test_f:

NoConcatenate

load

ID,

Date_t,

Time_t

Resident test_h

where Peek('ID')<> ID

order by Datetime desc;

drop table test_h;

drop table test;

Thanks,

Singh