Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Giansonn
Creator
Creator

What is the fastest way to get a max value in a group by table?

Hi, I am currently running a data load that looks like this:

 

[table1]:

Load id,

second_id,

timestamp

from example.qvd

 

Inner Join

load second_id,

max(timestamp) as timestamp

resident table1

group by second_id;

 

What this does is it finds only the latest entry by second_id and discards the rest of the entries. However I have tried to load and I know that this is an expensive query. I have seen other options such as `Peek` and `LOAD FieldValue(‘Id’, recno()) as Id AUTOGENERATE FieldValueCount(‘Id’)` and I want to know how to use these in my case.

Any help is much appreciated!

9 Replies
Saravanan_Desingh

I think your code looks good. Either INNER or RIGHT join can be used. But the other options like PEEK or AUTOGENERATE will be much more expensive than yours.

[table1]:
Load 	id,
	second_id,
	timestamp
from example.qvd
;

Right Join
Load 	second_id,
	max(timestamp) as timestamp
resident table1
group by second_id;
sunny_talwar

The approach provided here https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/ cannot be used with a group by of one or more field... it can only be used to get a max across the qvd. Only thing you can do is to avoid two loads here and do directly find the max from qvd load.

[table1]:
Load second_id,
     max(timestamp) as timestamp
from example.qvd
group by second_id;

 

Giansonn
Creator
Creator
Author

Hi Sunny, do you think loading from the QVD would be faster than the the INNER and RIGHT joins above? I agree that I have read this in online blog posts like here: https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/

 

The resident load is the slowest by far, which is why I was looking for other options.

sunny_talwar

I don't think it will be a huge improvement, but worth a shot. One thing which you can consider is Optimize Group By Performance 

 

Giansonn
Creator
Creator
Author

This is great, so I could order by then group by and I would save time in some cases.

However my query is relatively simple and you can only order tables that are already loaded so I would have to go back to a resident table load.

 

So now my choice is:

a) resident table with order by then group by

b) QVD load instead of resident load

 

I will send results as soon as they become available!

sunny_talwar

You can always Order your data before you store it into qvd. Then when you load the data from qvd you can directly use Group by. I guess all these are worth a shot, not sure what exactly will work the best.

Giansonn
Creator
Creator
Author

Thats the thing, the data is ordered by the timestamp so maybe going into the QVD is already the best solution

sunny_talwar

Order by is needed on second_id

Giansonn
Creator
Creator
Author

Oh yeh my mistake. I will give these data loads a try anyway