Add Recent Date Flag

[#1]

Hi All,

I have a table containing the three values:

Transaction Timestamp,

New Value,

Old Value.

I load them as shown:

LOAD ID,

     [Old Value] ,

     [New Value] ,   

     [Transaction Timestamp],

     Timestamp#([Transaction Timestamp],'mm/dd/yyyy hh:mm:ss') as 'Formatted Timestamp'

FROM

My Table;

What I would like is to have a flag in the load script to identify the most recent transaction. Can someone please help me here? Do I need a self join with group by or is there a way I can check the value and add a flag in the same table? I am struggling with the syntax here as the timestamp field also need to be formatted ( as shown above)

Thanks,

Praveen

[sujeetsingh]

Table2:

LOAD ID,

     [Old Value] ,

     [New Value] ,  

     [Transaction Timestamp],

     Timestamp#([Transaction Timestamp],'mm/dd/yyyy hh:mm:ss') as 'Formatted Timestamp'

    interval(Today()-[Transaction Timestamp],'dd') as Recent

FROM

My Table;

FinalData:

Load ID

,min(Recent)

resident Table2:

drop table Table2;

[SergeyMak]

Hi

Try to add this script

LEFT JOIN (YourTableName)

LOAD

ID,

Max([Transaction Timestamp]) AS [Transaction Timestamp],

1 AS Last

Resident YourTableName

GROUP BY ID;

Regards,

Sergey

[jagan · Accepted solution]

Hi,

Try like this

Data:

LOAD ID,

     [Old Value] ,

     [New Value] ,  

     [Transaction Timestamp],

     Timestamp#([Transaction Timestamp],'mm/dd/yyyy hh:mm:ss') as 'Formatted Timestamp'

FROM

My Table;

LEFT JOIN(Data)

LOAD

ID,

Max(Formatted Timestamp) as 'Formatted Timestamp',

1 AS MaxTimeStampFlag

RESIDENT Data

GROUP BY ID;

Hope this helps you.

Regards,

Jagan.