Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Replicating a Set Analysis in the Load Script

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

1 Solution

Accepted Solutions
Partner
Partner

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.

 

 

View solution in original post

2 Replies
Partner
Partner

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.

 

 

View solution in original post

Partner
Partner

This worked great! Thank you @_ylc !