Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
What if you try this:
Max(Timestamp(system_timestamp, 'YourTimestampFormat')) as Max_timestamp
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
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,