Timestamp and Number Format Issues

[Sara1]

I'm trying to load timestamp data from my database and using the following script to filter to records with max(timestamp). However, Qlik converts the timestamp to number format when using the max function so the where clause does not work correctly, therefore Qlik is returning 0 records. I've tried to convert the timestamp#(system_timestamp) using timestamp() to match the max(system_timestamp), tried num(timestamp#(system_timestamp), and also tried timestamp(max(system_timestamp) to convert this into timestamp format to match system_timestamp, but none of these have worked. I'd appreciate any other ideas. Script below uses sample data. Thank you! 

[Original_data]:

LOAD

serial_number,

first_name,

last_name,

job_title,

company,

num(timestamp#(system_timestamp)) as "system_timestamp"

;

//Find latest system_timestamp field in temp table

Temp: 

NoConcatenate

Load

max(system_timestamp) as "Max_timestamp”,

serial_number,

first_name,

last_name,

phone_num

Resident [Original_data]

group by

serial_number,

first_name,

last_name,

phone_num

;

Left Join([Original_data])

Load * Resident Temp;

Drop Table Temp;

Temp:

NoConcatenate

Load * Resident [Original_data]

where system_timestamp = "Max_timestamp";

Drop table  [Original_data];

Rename table Temp to  [Original_data];

[BrunPierre]

What if you try this:

Max(Timestamp(system_timestamp, 'YourTimestampFormat')) as Max_timestamp

[Sara1]

Thank you @BrunPierre, I tried that as well but it isn't working for this scenario unfortunately. Max(Timestamp(system_timestamp, 'MM-DD-YY hh:mm:ss.ffff TT')) as Max_timestamp

[RafaelBarrios]

Hi @Sara1 
you should try to first get the max and then change the format.

Timestamp(max(system_timestamp), 'MM-DD-YY hh:mm:ss.ffff TT') as Max_timestamp

but also note that the following script will not work this way.

Temp:

NoConcatenate

Load * Resident [Original_data]

where system_timestamp = "Max_timestamp";

you will need to recover that Max_timestamp value reading again the table and saving the field value into a variable with a peek('<field name>',<row number>,'<table name>') function 

hope it helps

best,