Replicating a Set Analysis in the Load Script

[morenoju]

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

[_ylc · Accepted solution]

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.

[morenoju]

This worked great! Thank you @_ylc !