Qlik Load values basis on latest timestamp/capturetime

[SurajD]

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

[Vegar]

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)?

[SurajD]

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

[jyothish8807]

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

[NitinK7]

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;

[SurajD]

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

[Vegar · Accepted solution]

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

[jyothish8807]

You can also try the syntax mentioned above by me by splitting into multiple tables.

[SurajD]

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