
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Qlik Load values basis on latest timestamp/capturetime
Hi Team,
I am using qlik sense ODBC connector and i am able to load all the data from particular table.
Issue : I am not able to load the vehicle id which are with the max capture time. Please refer below is my load script and what can i change in below script to load only vehicle id's which are having latest capture time.
LOAD "vehicleid",
"capturetime";
SQL SELECT "vehicleid",
"capturetime",
FROM "doc"."vehicle_detection";
But I wanted to load only those vehicle Id's which are having latest capture time.(this capture time can be changed basis on status of vehicle.) but it doesn't matter I just want to load vehicle ids with only latest capture time.
Please suggest do I need to use groupby or max timestamp ? and how can I use in my case ?
Really thanks in advance,
Regards,
Suraj Dighodkar
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you want to do it in the sql query then try someting like this:
SQL
SELECT
t.vehicleid ,
t.plant,
t.capturetime
FROM ( SELECT vehicleid , MAX(capturetime) AS max_capturetime
FROM vehicle_detection
GROUP BY matchnum ) AS m
INNER JOIN vehicle_detection AS t
ON t.vehicleid = m.vehicleid
AND t.capturetime = m.max_capturetime
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What is the expected output from the image sample you posted? Is it one (1) record for Z1201991004 (7/27/2020 1:49:11 PM) or is it two records Z1201991004 (7/27/2020 1:49:11 PM) and Z1201991000 (7/27/2020 1:48:56 PM)?
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Vegar
Really thanks for your quick reply ,
The expected output from image is Z1201991000 (7/27/2020 1:48:56 PM) and Z1201991004 (7/27/2020 1:49:11 PM) should get loaded. (latest time)
Thanks & regards,
Suraj Dighodkar


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
try like this:
Tmp_01:
LOAD "vehicleid",
"capturetime";
SQL SELECT "vehicleid",
"capturetime",
FROM "doc"."vehicle_detection";
Tab:
Load
vehicleid as Veh_ID,
max(capturetime) as Time
resident Tmp_01
group by vehicleid ;
Drop table Tmp_01;
Br,
KC
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
try like below
LOAD "vehicleid",
"capturetime";
SQL SELECT "vehicleid",
Max("capturetime") as capturetime
FROM "doc"."vehicle_detection"
group by vehicleid;
Note-
I strongly suggest you to first load data as it is no any transformation operation to do and save it to QVD format
and use qvd to load only max record
see below exmaple
LOAD "vehicleid",
"capturetime";
SQL SELECT "vehicleid",
"capturetime"
FROM "doc"."vehicle_detection";
store table into your path (QVD);
and then use this QVD
load "vehicleid",
Max("capturetime") as capturetime
from QVD file
group by vehicleid;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @jyothish8807 and @NitinK7
I am trying to implement solution suggest by you, but I am having other column also and because of that I am getting
"ErrorMsg: ERROR: 'plant' must appear in the GROUP BY clause or be used in an aggregation function. Perhaps you grouped by an alias that clashes with a column in the relations; Error while executing the query"
Is this occurring because of group by ?
Hi @NitinK7 I will definitely use QVD files as per your suggestions. but first I will have get rid of this error and need to verify weather i am getting excepted result or not.
Regards,
Suraj Dighodkar

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you want to do it in the sql query then try someting like this:
SQL
SELECT
t.vehicleid ,
t.plant,
t.capturetime
FROM ( SELECT vehicleid , MAX(capturetime) AS max_capturetime
FROM vehicle_detection
GROUP BY matchnum ) AS m
INNER JOIN vehicle_detection AS t
ON t.vehicleid = m.vehicleid
AND t.capturetime = m.max_capturetime
Qlik Community MVP


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can also try the syntax mentioned above by me by splitting into multiple tables.
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI @Vegar
This is what I expected, after adding the given SQL statements in qlik script and I have added other required attributes also and its working fine, even I am not getting group by issue. Thanks for the time & solution
Regards,
Suraj Dighodkar
