Error: Field 'start_date' not found

[morenoju]

Hi folks,

Sometime the reloading task of my Qlik Sense November 2020 app fails saying "Error: Field 'start_date' not found".

For what I see in the log, looks like the SQL Select query to my Postgres database doesn't return anything (which is OK, because sometimes there won't be any record matching the conditions) and then Qlik Sense fails when trying to load the results in the actual Qlik table.

Any idea on how to avoid this?

Pasting my script log:

0433 [traffic]:

0434 LOAD
0435 Date(Floor(ConvertToLocalTime(start_date,'Melbourne'))) AS calendar_date,
0436 Floor(Hour(Time(Round(Frac(Time(ConvertToLocalTime(start_date,'Melbourne'),'hh:mm')),1/96),'hh:mm'))) as calendar_hour,
0437 Interval(Round(Frac(Time(ConvertToLocalTime(start_date,'Melbourne'),'hh:mm')),1/96),'hh:mm') as link_time_15min,
0438 link_id,
0439 Round("speed_kmh") as "link_speed_kmh"

0440 SQL SELECT
0441 date_trunc('hour', latest_stats_interval_start) + date_part('minute', latest_stats_interval_start)::int / 15 * interval '15 min' as start_date
0442 ,id as link_id
0443 ,round(avg(latest_stats_speed),1) as speed_kmh
0444 FROM "public"."bluetooth_data"
0445 WHERE (date_trunc('hour', latest_stats_interval_start) + date_part('minute', latest_stats_interval_start)::int / 15 * interval '15 min' + interval '11' HOUR > '2020-12-19 08:00')
0446 AND date_trunc('hour', latest_stats_interval_start) + date_part('minute', latest_stats_interval_start)::int / 15 * interval '15 min'
0447 != date_trunc('hour', now()) + date_part('minute', now())::int / 15 * interval '15 min'
0448 group by start_date,id

Error: Field 'start_date' not found
Execution Failed
Execution finished.

[Vegar]

It looks to me as you do not have a field named start_date in your data source. You are creating it in the load. 

You can only group by on fields  that exists in the source data. The approach know would work is this

Tmp:

SQL SELECT
date_trunc('hour', latest_stats_interval_start) + date_part('minute', latest_stats_interval_start)::int / 15 * interval '15 min' as start_date, 
id as link_id, 
latest_stats_speed
FROM "public"."bluetooth_data"

WHERE.... ;

FINAL:

Noconcatenate Load start_date, link_id, max, round(avg(latest_stats_speed),1) as speed_kmh

RESIDENT Tmp 

Group by start_date, link_id;

Drop table Tmp ;