How to load latest records based on TIME

[Sonalidas1]

Hi ,

I am facing challenges,  where I need the latest record based on my date and time , date and time field I get from report number.

By using date(date#(replace(subfield(file, '_' -1),'-1',''),'DDMMYYYYhhmmss')'DD/MM/YYYY hh:mm') as Datetime,

When I used max date () I get error like Datetime field not found.

Can anyone one help me to get solution 

[sergio0592]

Hi,

Not sure i understand perfectly what you're trying to achieve but for be sure that your new field Date Time is recognized as TimeStamp you can try :

Timestamp#(date_field&time_field,'Format')
 

[Sonalidas1]

Date and time in one field, how to do, can you plz text me.

[sergio0592]

Can you send a screenshot your DateTime field?

[Sonalidas1]

Hi @sergio0592 

Get the solution but now I'm struggling with join the table with master table,

Mastertable:

IP,

Device,

Status

From xyz.xlsx;

Left join(Mastertable)

Data:

IP,

Date,

Complaint

From ABC.qvd;

Load 

Date(Max(date)) as maxdate,

Resident mastertable

Group by IP;

Exit script;

Can you tell me,how I will join this 

[MarcoWedel]

Please post some sample data and your expected result regarding the Timestamp interpretation question.
Once solved, please close this thread by accepting a solution.
For independent questions please open another thread with appropriate title.
thanks
Marco