Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have this temp_table in my load script:
[temp_traffic]:
LOAD
calendar_date,
calendar_hour,
link_id,
link_time_15min,
link_speed_mph
FROM $(_vFileNameTraffic) (qvd);
I would need to store in a variable the latest link_time_15min available for the latest calendar_date.
I know how to do it in an expression of a sheet:
FirstSortedValue({1<calendar_date={"$(=date(Max(calendar_date)))"}>}DISTINCT [link_time_15min],-[link_time_15min],1)
I get the latest "link_time_15min" for the latest "calendar_date". For example "16:00" read from "2/28/2020".
How can I do the same in the load script?
Thanks,
Juan
Hi Juan,
What you can do is take the latest calendar_date and link_time_15min, create a timestamp to identify the maximum.
LOAD max(Timestamp(calendar_date + time(link_time_15min))) as mx
resident temp_traffic;
let vMX = Peek('mx', -1); //creat a variable and store the maximum date based on the load script above.
Load the table with link_time_15min as the only field and use a where condition where the date is the same as the maximum date.
LOAD distinct link_time_15min
resident temp_traffic
where Timestamp(calendar_date + time(link_time_15min))='$(vMX)';
let vLatest = Peek('link_time_15min', -1); //store the latest link_time_15min in a variable.
Hi Juan,
What you can do is take the latest calendar_date and link_time_15min, create a timestamp to identify the maximum.
LOAD max(Timestamp(calendar_date + time(link_time_15min))) as mx
resident temp_traffic;
let vMX = Peek('mx', -1); //creat a variable and store the maximum date based on the load script above.
Load the table with link_time_15min as the only field and use a where condition where the date is the same as the maximum date.
LOAD distinct link_time_15min
resident temp_traffic
where Timestamp(calendar_date + time(link_time_15min))='$(vMX)';
let vLatest = Peek('link_time_15min', -1); //store the latest link_time_15min in a variable.
This worked great! Thank you @_ylc !